Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-18-2017, 03:13 PM
rgauss rgauss is offline Calculate counts between fluid rows - TIME Windows 7 64bit Calculate counts between fluid rows - TIME Office 2013
Novice
Calculate counts between fluid rows - TIME
 
Join Date: May 2016
Posts: 6
rgauss is on a distinguished road
Default Calculate counts between fluid rows - TIME

Hello and Good Day to you -



I have a spreadsheet containing a column of times that increase every day. These time ranges are formatted in military time and viewed by the hour 6:00 - 7:00AM, 7:00 - 8:00AM, etc through 16:00. The root data is entered as (example) 6:53:07 AM

Each day these rows increase with new times, so for example the data that was in column B15 might be in B30 tomorrow. I am trying to find a way to track the Count of the number of entries for each hour spread, in other words how many events are found between 6 and 7, then 7 and 8, so forth and so on.

I have tried using COUNTIF but just cannot seem to get it right. Attached is an XLS that shows the data - column B is what I'm looking at. The counts in the row at the top D2:M2 are manual and I am trying to find a way to autopopulate these counts in the row as the data continues to be entered into the columns each day.

I did see a thread pointing to a format similar to this - =COUNTIF(B2:B500,>"6*")-COUNTIF(B2:B500,<"7*") but this failed. Have also tried doing a like COUNTIF(B2:B500,"6*") to get all of the counts for anything 6AM-related but that failed as well.

Any ideas on how I might achieve this result? Thanks so much for your time.
I am using OFFICE2013
Attached Files
File Type: xlsx For the forum.xlsx (18.6 KB, 12 views)
Reply With Quote
  #2  
Old 01-18-2017, 03:44 PM
jeffreybrown jeffreybrown is offline Calculate counts between fluid rows - TIME Windows Vista Calculate counts between fluid rows - TIME Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

How about a quick and simple pivot table setup. As new data is entered at the bottom of the data set, just right click the pivot table and refresh.
Attached Files
File Type: xlsx Group by time.xlsx (26.0 KB, 7 views)
Reply With Quote
  #3  
Old 01-18-2017, 03:54 PM
rgauss rgauss is offline Calculate counts between fluid rows - TIME Windows 7 64bit Calculate counts between fluid rows - TIME Office 2013
Novice
Calculate counts between fluid rows - TIME
 
Join Date: May 2016
Posts: 6
rgauss is on a distinguished road
Default

Thanks Jeffrey,

I will do that. I have pivots elsewhere in the workbook looking at data those times are associated with, suppose that would be the way to go here as well.

Appreciate your time, Thanks again,

Rob
Reply With Quote
  #4  
Old 01-18-2017, 04:09 PM
jeffreybrown jeffreybrown is offline Calculate counts between fluid rows - TIME Windows Vista Calculate counts between fluid rows - TIME Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Hi Rob,

You are welcome and thanks for the feedback.

If you want a formula solution, this seems to work on your sample date...

D2 copied to the right >> =COUNTIF($B$2:$B$431,"<"&TIME(7,0,0)+(0.0416666666 666667*(COLUMN(A1)-1)))-SUM($C$2:C2)
Reply With Quote
Reply

Tags
countif time

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate Time in Hours and Percent pabyford Excel 6 12-13-2016 12:29 PM
Calculate counts between fluid rows - TIME Need a formula to calculate turnaround time Rockitman31 Excel 9 09-07-2015 09:41 AM
Calculate counts between fluid rows - TIME How do I calculate planned work for a given time period bumpui Project 1 06-04-2014 06:37 AM
Formula to calculate Date and Time funkyfido Excel 2 06-25-2013 04:25 PM
Calculate counts between fluid rows - TIME How to get cells to calculate time? jrasche2003@yahoo.com Excel 2 02-09-2007 07:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:42 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft