calc elapsed time

[MRHOHO]

Using military time how do I go about calculating the elapsed time given a starting time and a finished time?

 

[Faseeh]

Hi MRHOHO,

Please use:

=TEXT(TIMEVALUE(B2)-TIMEVALUE(A2),"HH:MM")

Where A2 is start time, B2 is end time.

 

[NARAYANK991]

Hi ,

From what I have seen on Wikipedia , military time is just time using a 24-hour clock ; in this case , the elapsed time is a simple subtraction of the start time from the end time ; for example , if your data is as follows :

end time - A1 : 17:35:45

start time - B1 : 07:15:33

Then , a formula such as =A1-B1 will give 10:20:12

The problem may crop up when the end time is less than the start time , presumably because it is on the next day ; thus if A1 were to contain 07:15:33 , and B1 were to contain 17:35:45 , then you need to use a formula such as :

=IF(end_time<=start_time,end_time+1-start_time,end_time-start_time)

If the end time is the same time as the start time , the above formula will give a result of 24 hours , which however will not be displayed , unless you change the cell format to :

[hh]:mm:ss

If you want that when the two times are equal , the result should be 0 , then use the formula :

=IF(end_time<start_time,end_time+1-start_time,end_time-start_time)

Narayan

 

[SirJB7]

@NARAYANK991
Hi!
AFAIK military time uses integer numbers (mathematically speaking, not VBA speaking) from 0 to 235959 to represent the hours (10^5 and 10^4 digits), minutes (10^3 and 10^2 digits) and seconds (10^1 and 10^0 digits), with the related discrete holes for minutes and seconds ranging from 60 to 99. At least when I was asked to do something military time related I was told so.
E.g., 190000, one hundred and ninety thousands it's 7:00:00 PM, but 1900, one thousand and nine hundred it's 7:00 PM too if in that notation seconds were not considered. A bit cumbersome.
Regards!

 

[NARAYANK991]

Hi Pablo ,

Let the OP clearly specify which of the following ( taken from Wikipedia ) apply in their case :

In Canada and the United States, the term "military time" is a synonym for the 24-hour clock.[7] In these regions, the time of day is customarily given almost exclusively using the 12-hour clock notation, which counts the hours of the day as 12, 1, ..., 11 with suffixes "a.m." and "p.m." distinguishing the two diurnal repetitions of this sequence. The 24-hour clock is commonly used there only in some specialist areas (military, aviation, navigation, tourism, meteorology, astronomy, computing, logistics, emergency services, hospitals), where theambiguities of the 12-hour notation are deemed too inconvenient, cumbersome, or outright dangerous.
In the United States military, military time is similar to the 24-hour clock notation, with the exception that the colon is omitted and the time on the hours is often spoken as its decimal value. For instance, 6:00 a.m. would become 0600, and would be spoken "zero six hundred" or "zero six hundred hours" (for example, when said face-to-face), "oh six hundred" (colloquially), or "zero six zero zero" (for example, where clarity is needed when specifying the time over a radio or sound-powered telephone). Hours are always "hundred", never "thousand"; 10:00 is "ten hundred" not "one thousand", 20:00 is "twenty hundred".
Military usage differs in some respects from other twenty-four-hour time systems:

• Written military time does not usually include a time separator (for example, "0340" is more common than the civilian "03:40").
• Leading zeros, always written out by the military, are often also spoken in military usage, so 5:43 a.m. is often spoken "zero five forty-three" (military) or "zero five four three" (military radio), as opposed to "five forty-three" (civilian).
• Military time zones are lettered and thus given word designations via the NATO phonetic alphabet. For example, 6:00 a.m. Eastern Standard Time (GMT-5) would be written "0600R" and spoken, "zero six hundred Romeo".
• Local time is specifically designated in the military as zone J or "Juliet". A time of "1200J" ("twelve hundred Juliet") corresponds to noon local time.
• Greenwich Mean Time (or Coordinated Universal Time) is designated as zone Z, and thus called "Zulu time".

Narayan

 

[MRHOHO]

Hi MRHOHO,

Please use:

=TEXT(TIMEVALUE(B2)-TIMEVALUE(A2),"HH:MM")

Where A2 is start time, B2 is end time.

 

[MRHOHO]

Hi MRHOHO,

Please use:

=TEXT(TIMEVALUE(B2)-TIMEVALUE(A2),"HH:MM")

Where A2 is start time, B2 is end time.

Thanks for your help.

@NARAYANK991
Hi!
AFAIK military time uses integer numbers (mathematically speaking, not VBA speaking) from 0 to 235959 to represent the hours (10^5 and 10^4 digits), minutes (10^3 and 10^2 digits) and seconds (10^1 and 10^0 digits), with the related discrete holes for minutes and seconds ranging from 60 to 99. At least when I was asked to do something military time related I was told so.
E.g., 190000, one hundred and ninety thousands it's 7:00:00 PM, but 1900, one thousand and nine hundred it's 7:00 PM too if in that notation seconds were not considered. A bit cumbersome.
Regards!

 

[SirJB7]

Hi, MRHOHO!
Would you mind telling us (just because of curiosity) how are the military time values?
Regards!