COUNTIFS specific range of time

0

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. 

Answer
Discuss

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.

Discuss

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.
Variatus (rep: 3243) Nov 17, '19 at 8:35 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login