E. M.

asked • 06/23/21

Comparison Time Range formula

Hello,

I'm currently using a formula that counts the number of entries that fall between a certain time range. The error I receive is for an entry at 10:03 PM not being counted between the time range of 10:01 PM and 12:00 AM. My current formula is as follows:

=COUNTIFS(O4:O48,">="&A4,O4:O48,"<="&B4)

Where A4 is 10:01 PM and B4 is 12:00 AM

Derrick M.

tutor
What format are you using to enter your time? Also would you be able to add a snippet of the sheet. Would like to see what all is in O4:O48 and what your current output is using your current formula.
Report

06/24/21

Patrick B.

Also for rollover, you may have to include the date. 10:01 pm and 12:00 am may always be false because its midnight the same day.
Report

06/24/21

1 Expert Answer

By:

Bryson G. answered • 06/30/21

Tutor
5.0 (1,494)

Dedicated STEM Tutor Passionate for Teaching for Understanding
About this tutor ›
About this tutor ›

I think your issue is that Excel internally treats times as a decimal fraction of 24 hours so 10:01 PM is "0.917361111" and 12:00 AM is "0". Therefore nothing is both greater than 0.917... and less than 0. I'm assuming this sheet needs to be designed so the start-end times in cells A4 and B4 can be varied so your formula just needs a check-test to only change midnight to "1" full day instead of the very beginning "0" of another day.

=COUNTIFS(O4:O48,">="&A4,O4:O48,"<="&IF(B4=0,1,B4))

Upvote • 1 Downvote
More
Report

Still looking for help? Get the right answer, fast.

Ask a question for free

Get a free answer to a quick problem.
Most questions answered within 4 hours.

OR

Find an Online Tutor Now

Choose an expert and meet online. No packages or subscriptions, pay only for the time you need.