Time Issue at Midnight

[KuraiChikara]

I have an issue with a COUNTIF formula once a schedule goes to midnight and after.
In column A I have four times; 21:00, 22:00, 23:00 and Midnight.

The first formula only looks at A2-A4; omitting midnight: Count, If A2 through A4 is greater than or equal to 9:00 PM, the result is 3; this is fine.
=COUNTIF(A2:A4,">=21:00")

However, in the sense of working schedules, not a standard day, someone who works 4pm to 1am is considered to work later than someone who works 1pm to 10pm, so from a verbiage standpoint you can say 1am is later than 10pm but Excel doesnt like that.

In the second formula, I'm including midnight cell A5, but it doesnt count midnight because midnight in technical terms is not "Greater" than 9pm; it's earlier and I don't know how to represent that.

=COUNTIF(A2:A5,">=21:00")


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Formula 1[/TD]
[TD]Formula 2[/TD]
[/TR]
[TR]
[TD]21:00[/TD]
[TD]3[/TD]
[TD]3 (my goal is 4)[/TD]
[/TR]
[TR]
[TD]22:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

I'm not sure exactly what you want to do, but a quick and dirty way to work round this is to use 23:59 instead of 00:00.

This might or might not work for you, depending on what you want to do exactly.

 

[Joe4]

However, in the sense of working schedules, not a standard day, someone who works 4pm to 1am is considered to work later than someone who works 1pm to 10pm, so from a verbiage standpoint you can say 1am is later than 10pm but Excel doesnt like that.

What you are describing is a period over multiple days. So I don't think you can do this strictly with time. I think you need a day component too. Then you should be able to do what you want.

Note that Excel actually stores all time values with date components. You just format it to "hide" the date piece.
Excel stores dates/times as the number of days since 1/0/1900 (enter any date, and change the format to "General" and you will see this).
Dates are just whole numbers, and times are fractions of one day.

So if you enter today's date and a time like this:
5/30/2019 6:00 AM
and convert the cell format to General, you will see it as Excel does:
43615.25

 

[KuraiChikara]

You actually helped remind me with an older process that I used to use. Japan extends the 24 hour clock further in some situations and I forgot that Excel interprets this; IE 23:00=11pm, 24:00=midnight, 25:00=1:00am, 26:00=2:00am etc. This method works. Using my formula in example 2, if I change midnight from 00:00 to 24:00 it works like a charm.