Change EXCEL Clock to Standard Clock or Military Time

[Guest]

2002 Excel's clock runs from Midnight of the day before to 11:59 PM of the
current day. I need to either find a way to make Excel work on a Standard
Clock or a Military Clock.

 

[Harlan Grove]

2002 Excel's clock runs from Midnight of the day before to 11:59 PM of the
current day. I need to either find a way to make Excel work on a Standard
Clock or a Military Clock.

Midnight is ambiguous. 0.000000000000001 seconds before midnight is the day
before, 0.000000000000001 seconds after midnight is the current day. By the
time you're becoming apoplectic, it'll legitimately be the current day.

As a practical matter, integer date values formatted with time components
will appear as either 00[:00[:00]] or 12[:00[:00]] AM, never as
24[:00[:00]]. The only way you could change this is to display times as
text, e.g.,

=IF(MOD(DT,1)=0,TEXT(DT-1,"mm/dd/yyyy ""24:00"""),TEXT(DT,"mm/dd/yyyy"))

but this will never show midnight as the beginning of a day.

As for 'standard clock', standard in what country? The US NIST suggests
using neither 12 AM or PM but noon and midnight instead, and it's silent on
whether exactly midnight is the end of the earlier day or the beginning of
the later day.

 

[Guest]

2002 Excel's clock runs from Midnight of the day before to 11:59 PM of the
current day. I need to either find a way to make Excel work on a Standard
Clock or a Military Clock.

Midnight is ambiguous. 0.000000000000001 seconds before midnight is the day
before, 0.000000000000001 seconds after midnight is the current day. By the
time you're becoming apoplectic, it'll legitimately be the current day.

As a practical matter, integer date values formatted with time components
will appear as either 00[:00[:00]] or 12[:00[:00]] AM, never as
24[:00[:00]]. The only way you could change this is to display times as
text, e.g.,

=IF(MOD(DT,1)=0,TEXT(DT-1,"mm/dd/yyyy ""24:00"""),TEXT(DT,"mm/dd/yyyy"))

but this will never show midnight as the beginning of a day.

As for 'standard clock', standard in what country? The US NIST suggests
using neither 12 AM or PM but noon and midnight instead, and it's silent on
whether exactly midnight is the end of the earlier day or the beginning of
the later day.

"YoMarie" wrote:

Thank you for the response.

I understand the concept of "splitting hairs". However, I've taken on the
project for a small business of creating a Time Card that will calculate
itself. The company works on a standard US clock, that begins the day at
12:01 AM and ends the day at 12:00 PM (Midnight). They have operated on this
clock for over 25 years. So I need to find a solution.

 

[Harlan Grove]

project for a small business of creating a Time Card that will calculate
itself. The company works on a standard US clock, that begins the day at
12:01 AM and ends the day at 12:00 PM (Midnight). They have operated on this
clock for over 25 years. So I need to find a solution.

First, if you call 12:00 PM midnight, what's noon?

You'll need to use text formulas.

=IF(TEXT(DateTime,"hh:mm")="00:00",
TEXT(DateTime-1,"mm/dd/yyyy ""Midnight"""),
TEXT(DateTime,"mm/dd/yyyy hh:mm AM/PM"))

 

[Alan Beban]

First, if you call 12:00 PM midnight, what's noon?

From the meaning of "M" in AM and PM, one would expect that noon is
12:00M, since it is neither before (ante) nor after (post) the meridian;
but I haven't yet found anyone who can stomach that.

Alan Beban