# Thread: Calculating time difference in military time

1. ## Calculating time difference in military time

I need to calculate the time difference using military tome to the seconds 00:00:00

They want to enter the times not using the colons 235822 and have it convert to military time 23:58:22 which is simple enough using a custom format of 00\:00\:00

the issue is when I try to find the deference in time or how long it took to complete a task using the formula in cell C1 =IF((B1-A1)<0,(B1-A1)*24+24,(B1-A1)*24) I even tried =B1-A1+(A1>B1)

this formula would need to account for the change of days.

I get errors is there a way to simplify the data entry by not using colons

Attached is what I would like the finished product to look like only the end user would enter the data with out the additional use of Colons

2. Originally Posted by nsmjc1
I need to calculate the time difference using military tome to the seconds 00:00:00

They want to enter the times not using the colons 235822 and have it convert to military time 23:58:22 which is simple enough using a custom format of 00\:00\:00

the issue is ...
It is NOT a time value just a number formatted to look like the time.

The only way I know to get around this is via a macro that will rip the number apart and reconstruct it as time value. However, this would require that the entered value always have six digits because any less would make it impossible for the macro to tell which value was missing a digit. Of course, having said that someone will probably prove me wrong with a very long and complicated piece of code.

If you can live with having macros in the file and requiring 6 digits (actually the macro can enforce this) I'll be glad to gen up some code for you.

HTH

3. Originally Posted by RetiredGeek
It is NOT a time value just a number formatted to look like the time.

The only way I know to get around this is via a macro that will rip the number apart and reconstruct it as time value. However, this would require that the entered value always have six digits because any less would make it impossible for the macro to tell which value was missing a digit. Of course, having said that someone will probably prove me wrong with a very long and complicated piece of code.

If you can live with having macros in the file and requiring 6 digits (actually the macro can enforce this) I'll be glad to gen up some code for you.

HTH
That would be awesome macro is OK as long as it runs in the back ground and the user only needs to enter the numbers

4. nsmjc1,

Well it looks like I'll make a lair out of myself!

Here's a user defined function (udf) that will take two cell arguments and return the time difference.
Code:
```Option Explicit

Function CvtFmtTimeToTimeValue(rngStart As Range, rngEnd As Range) As Date

Dim zFmtText As String
Dim tmStart  As Date
Dim tmEnd    As Date

zFmtText = rngStart.Text

tmStart = TimeValue(Left(zFmtText, 2) & ":" & _
Mid(zFmtText, 4, 2) & ":" & _
Right(zFmtText, 2))

zFmtText = rngEnd.Text

tmEnd = TimeValue(Left(zFmtText, 2) & ":" & _
Mid(zFmtText, 4, 2) & ":" & _
Right(zFmtText, 2))

If (tmEnd < tmStart) Then tmEnd = tmEnd + 1

CvtFmtTimeToTimeValue = tmEnd - tmStart

End Function  'CvtFmtTimeToTimeValue```
You call this by using this formula: =CvtFmtTimeToTimeValue(A1,B1)
Where: the first argument is the start time (A1 in this case)
the second argument is the end time (B1 in this case)

nsmjc1.JPG

You must format the cells containing the entered times as per your original post!
The cells with the calculation must be formatted with the custom format: hh:mm:ss
The user must enter enough digits on the right of the number to clarify the time position, e.g.

If only seconds: 3 = 3 seconds, 30 = 30 seconds
If only minutes: 300 = 3 minutes, 3000 = 30 minutes
etc.

Sample File: nsmjc1.xlsm

5. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

nsmjc1 (2015-11-02)

6. Originally Posted by nsmjc1
...this formula would need to account for the change of days.
Just to clarify something... If you mean the difference between 2300 on Monday and 0700 on Tuesday, that's fairly clear because the second number is lower. But what about 1700 on Monday to 1900 on Tuesday as opposed to 1700 to 1900 on Monday?

7. Graham,

I assumed, yeah I know big mistake, that since the OP was only working with times it probably wouldn't span more than 24 hours. If that is the case the entire date/time value will have to be added. I'll wait to see what the OP has to say.

8. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

nsmjc1 (2015-11-02)

9. Originally Posted by RetiredGeek
I assumed, yeah I know big mistake, that since the OP was only working with times it probably wouldn't span more than 24 hours.
I ask because I've been bitten by this before now.

10. that worked out perfectly we are only concerned with a mater of few minutes or a couple of hours it will not span days thank you for all you help

11. Hi

Or maybe a formula if you do not want to use code.

=MOD((--TEXT(B2,"00\:00\:00"))-(--TEXT(A2,"00\:00\:00")),1) copy down and format as "hh:mm:ss or [h]:mm:ss".

12. Hi all

..let's be clear here, are we talking about Zulu time?????
..and if not, what military unit are we referring to???

The Navy, as well as civil aviation, uses the letter "Z" (phonetically "Zulu") to refer to the time at the prime meridian. The U.S. time zones are Eastern ["R", "Romeo]; Central ["S", "Sierra"]; Mountain ["T", "Tango"]; Pacific ["U", "Uniform"]; Alaska ["V", "Victor"], and Hawaii ["W", "Whiskey"].

..give me Whiskey time any day.
(I prefer Rum, but, hey, Hawaii is such a great place)

zeddy

13. zeddy, you are talking about timezones. Military time is a different concept, wherein you never say (am or pm), instead you use a 24-hour clock. It's just a different way of distinguishing before Noon or after Noon.

nsmjc1 doesn't have to be concerned with timezones unless he has users updating a spreadsheet from different timezones. Which, while certainly possible, isn't the simple use-case. In fact I cannot remember designing a spreadsheet where timezones mattered.

The simple use-case is "local time", whatever timezone that is.

14. ..perhaps you are just using a '24-hour-clock', so maybe you could just use standard GMT.

With military time: for instance, 6:00 a.m. in zone UTC−5 is written "0600R" and spoken "zero six hundred Romeo".
The letter J ("Juliet") is used to indicate the observer's local time, as in 2100J.

For those interested in further explanations, here are some links:
Military Time:
http://www.timeanddate.com/time/zones/z

https://en.wikipedia.org/wiki/List_o...ary_time_zones

Coordinated Universal Time
https://en.wikipedia.org/wiki/Coordi...Universal_Time

zeddy

15. ..in the Caribbean, there is a mix of date formats and time zones in use.
It took me a while to properly master the issues raised in Excel when automating the updating of files from multiple zones in the region. It is not a simple issue to deal with. I don't suppose many users get exposed to operating system filedate timestamps that can be in 'the future' depending on where you are. It can be interesting to see files that appear to be 'last modifed' half-an-hour from now!

zeddy

16. The problems are
1. the sheet needs to know the date of each time references. To do this, you need a date column for each time which, of course, can be hidden.
2. If you intend to have a total of hours that is greater than 23:59:59, or one day, then this format is not going to work, because that is the limit of the function and after that, it will produce an error.

So you have to have
a column for the first date – with the cell formatted as a date (right click choose ‘format cell’)
a column for the first time – with the cell formatted as hh:mm:ss (right click choose ‘format cell’)
a column for the second date – with the cell formatted as a date (right click choose ‘format cell’)
a column for the second time – with the cell formatted as as hh:mm:ss (right click choose ‘format cell’)
So you have

......A..............B....................C....... ........D.....................E
Start date ..| Start time |…. End Date…. | End time | = (C2-A2) + (D2-B2)
01/11/2015…. .23:00:00..…..02/11/2015.… 02:00:00….= 3:00:00

17. Zeddy,

The OP is more correctly referring to a 24hr clock (which is used in a number of European countries) than military time, even though 24hr is often referred to as military time in the US.

It's unfortunate that military and 24hr time notations have been mixed because they are different (0900 vs 9:00) and military times are frequently Zulu/GMT/UTC times rather than local. For example, 7pm would be 19:00 (spoken as 19 o'clock) in 24hr time or 1900hr (spoken as nineteen-hundred hours) in military time.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•