Help with time formula to count cells below a time (some pass midnight) I have a

Question

Help with time formula to count cells below a time (some pass midnight)

I have a spread sheet which has times

I need to count how many are below a set time but due to some going past midnight i cannot get a true answer

example below

I have tried the COUNTIF but this fails to count correctly

this is what i have tried second line =COUNTIF(C23:G23,"<3:01 AM") result was 0

Any help with a formula please

Thank You

1:05 AM 12:55 AM 1:03 AM 12:25 AM 1:00 AM 12:50 AM 12:52 AM 12:58 AM 12:10 AM 12:33 AM 12:59 AM 3:07 AM 12:51 AM 2:30 AM 12:06 AM

Explanation / Answer

I am trying this in Excel 2013

=COUNTIF(C23:G23,"<"&TIME(3,1,0))

=COUNTIF(C23:G23,"<3:01")

=COUNTIF(C23:G23,"<3:01 AM")

The only reason it won't work is that your data is not in TIME format but in text format. Please select a cell and see whether it is in TIME format or not.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.