Time Function 24:00 TIme(?????)

[Gerhardstru]

Hi

Can you please assist in how to countifs time between 2 values. Specifically if the one value falls on Midnight


-- removed inline image ---


Formula in C5 =COUNTIF(F5:XFD5,"<="&TIME(19,30,0)) This seems to work correct, I want to count times smaller than 19:00

Formula in D5 =COUNTIFS(F5:XFD5,">="&TIME(19,30,1),F5:XFD5,"<="&TIME(21,30,0)) Also seems correct, I want to count between >19:300 and =<21:30

Formula in E5 =COUNTIFS(F5:XFD5,">="&TIME(21,30,1),F5:XFD5,"<="&TIME(23,59,59)) This is where I have my problem. I want to count values =>21:30 and <=24:00

I changed the formula to 23:59:59 to test, it seems to work, The problem I have is that there are values showing as 24:00 or 12:00 PM

 

[Gerhardstru]

Ok, pic did not attache and no idea how to get it attached

 

[Gerhardstru]

Hi

Can you please assist in how to countifs time between 2 values. Specifically if the one value falls on Midnight


-- removed inline image ---


Formula in C5 =COUNTIF(F5:XFD5,"<="&TIME(19,30,0)) This seems to work correct, I want to count times smaller than 19:00

Formula in D5 =COUNTIFS(F5:XFD5,">="&TIME(19,30,1),F5:XFD5,"<="&TIME(21,30,0)) Also seems correct, I want to count between >19:300 and =<21:30

Formula in E5 =COUNTIFS(F5:XFD5,">="&TIME(21,30,1),F5:XFD5,"<="&TIME(23,59,59)) This is where I have my problem. I want to count values =>21:30 and <=24:00

I changed the formula to 23:59:59 to test, it seems to work, The problem I have is that there are values showing as 24:00 or 12:00 PM

 

[Jonmo1]

Formula in E5 =COUNTIFS(F5:XFD5,">="&TIME(21,30,1),F5:XFD5,"<="&TIME(23,59,59)) This is where I have my problem. I want to count values =>21:30 and <=24:00

Try
=COUNTIFS(F5:XFD5,">="&TIME(21,30,1),F5:XFD5,"<=1")

 

[Gerhardstru]

Try
=COUNTIFS(F5:XFD5,">="&TIME(21,30,1),F5:XFD5,"<=1")

Ok gave that a go, that increased my count in C5 but not in E5

I can sort out the C5 with a more than function. But E5 is still a problem

 

[Jonmo1]

I think the problem is that a time of EXACTLY Midnight is actually considered AM, and will be counted by the C5 formula counting time smaller than 19:00
Midnight is actually 0 hours
24:00:00 is actually 12AM of the next day.

So Midnight is NOT > 21:30:00

If you wanted to count Midnight with the E5 formula and NOT the C5 formula, you'd have to
C5: count > 0:00:00 AND <= 19:30:00
E5: count >= 21:30:01 OR = 0:00:00 <- this is the tricky one.

C5: =COUNTIFS(F5:XFD5,">"&TIME(0,0,0),F5:XFD5,"<="&TIME(19,30,0))
E5: =SUM(COUNTIF(F5:XFD5,{"0:00:00",">=21:30:01"}))