I have a timestamp column. It is formatted as time (1:30 PM). I used the =NOW() to fill in the timestamp. I would like to do a COUNTIFS formula where it counts how many times from a specific timeframe like 6:00 pm to 7:00 pm.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
COUNTIFS specific range of time
0
Answers
0
Please try a formula like this one.
=COUNTIFS($A$2:$A$10,">="&$H$2,$A$2:$A$10,"<"&$H$3)Here the times are in the range A2:A10, the start time is in H2 and the end time in H3. The start time is included in the count but not the end time, to avoid double counting if you count from 6:00 to 7:00 and from 7:00 to 8:00.
You may face some difficulty entering the times correctly. It's vital that the same format is used in both column A and column H and that this format is a correct Time format, not a text string. Test your entries by formatting the testing cell as Number.
Presuming that a cell in column A is formatted as either Time or General, and you then enter 13:00 the cell will display either 13:00 or 1:00 PM, depending upon the time format you set for Windows (Excel uses the setting you entered in the Control Panel). Now change the NumberFormat (Right-click the cell > Format cells > Number Format) to Number. The cell will display 0.54. Repeat the test in column H.
Discussion
Thank you for the response. I have done that as well. I have found out that the =now() still shows a date and time. So I have changed it to =now()-int(now()) and now it works.
Jagra Nov 17, '19 at 2:41 am
I'm glad that you got uyour problem solved.
For the benefit of other visitors who read our thread please do one of the following. Either create an answer, post the final formula you used, and mark your own answer as "Accepted" or, if you find your own answer heavily relying upon my suggestion, just mark my answer as "Accepted". Thank you.
For the benefit of other visitors who read our thread please do one of the following. Either create an answer, post the final formula you used, and mark your own answer as "Accepted" or, if you find your own answer heavily relying upon my suggestion, just mark my answer as "Accepted". Thank you.
Variatus (rep: 4889) Nov 17, '19 at 8:35 pm