Military time

[Gene]

I am copying and pasting data from a website that uses 24:00 as midnight rather than 00:00. Is there formatting that will allow me to use that? As it is now 24:00 is changed to 00:00 and it is throwing calcs off by a day
Thanks in advance
Gene

 

[Peo Sjoblom]

24:00 is the same as 00:00, you can use a custom format to display it like
24:00 [hh]:mm


--

Regards,

Peo Sjoblom

I am copying and pasting data from a website that uses 24:00 as midnight

rather than 00:00. Is there formatting that will allow me to use that? As
it is now 24:00 is changed to 00:00 and it is throwing calcs off by a day

 

[Harlan Grove]

24:00 is the same as 00:00, you can use a custom format to display it like
24:00 [hh]:mm

...

The problem is that exactly midnight in military time is 2400 but 1 minute after
midnight is 0001. If these would always be time of day, so never exceeding
exactly 24 hours, and adding colons, the OP may be better off with the number
format

[=0]"24:00";[=1]"24:00";hh:mm

 

[Harlan Grove]

This is getting closer but still not quite it. Transaction starts
Wed Feb-04-04 at Midnight(2400) and ends on Thurs Feb-05-04 at 0100.
If I subtract these 2 I should get 1 hour, not 25 hours.

Unavoidable ambiguity. It may be that the reason military time shows midnight as
2400 is because it treats the minute from exactly midnight to 12:01 AM as part
of the preceding day. That definitely seems to be the case here. Unfortunately,
Excel treats that minute as part of the next day. To be precise, Excel considers
that days begin exactly at 1 second after 11:59:59 PM the previous day.

The good news is that Excel will handle the date/time entry 2004-2-4 24:00 as
2004-2-5 00:00. Do you have dates and times in separate cells?

 

[Gene]

Ive got it working now.
Thanks!

 

[John McGimpsey]

Unavoidable ambiguity. It may be that the reason military time shows midnight
as
2400 is because it treats the minute from exactly midnight to 12:01 AM as
part
of the preceding day. That definitely seems to be the case here.
Unfortunately,
Excel treats that minute as part of the next day. To be precise, Excel
considers
that days begin exactly at 1 second after 11:59:59 PM the previous day.

Actually, when I was in the military, at least, 24:00 was incorrect,
even aside from using colons. We may have informally *said* "twenty-four
hundred hours", but in written form it was always 000000Z, in order to
avoid just that ambiguity.

Also, 2400 was always used in the connotation of end of the day, as in
"I get off-watch at 2400 hours". Something starting at midnight always
used 0000 as in "The exercise starts at zero hundred hours", or "I've
got the all-balls to oh-six-hundred watch"

 

[Norman Harker]

Hi JE

Time representation is covered by ISO8601:2000. These Standards are
subscribed to by most countries of the World and are aimed at removing
ambiguity.

5.3.2
Allows representation of Midnight as:

000000 or in separated form 00:00:00
240000 or in separated form 24:00:00

5.3.1.2
Allows truncation to

0000 or 00:00
2400 or 24:00

NOTE 2 to 5.3.2
"The choice of representation a) or b) will depend upon any
association with a date, or a time interval. Representations where
[hh] has the value [24] are only preferred to represent the end of a
time interval in accordance with 5.5.1"

My comment. This makes it pretty clear. Use 0000 for midnight unless
you are dealing with a time period ending in midnight in which case
(e.g.) 1800 to 2400 is acceptable.


5.3.3 Coordinated Universal Time (UTC)
To express a time in UTC you should follow the time (without a space)
by the designator Z.

This gives us: 0000Z or 00:00Z

Where local time is to be represented use:

0000+1000
or
00:00+10:00

My comment: In this representation there is an assumption that the
base time is UTC but I suspect that most would designate this clearly
as UTC 0000+1000


Outside the ISO8601:2000 many countries with different time zones such
as USA and Australia will use a clear designator such as AEST
(Australian Eastern Standard Time)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.