Calculating Elapsed Time in Military Format

[cottonangel]

Greetings -

I am trying to calculate the difference in times, using military time. For example:

F2 I have entered 16:30 (have a formula entered 00\:00 so I can type in 1630 and it will auto change it to 16:30).
H2 I have entered 21:00 (with same formula as F2 for easy entry).
J2 I am trying to determine the lapsed time between 1630 and 2100.

I have tried what feels like a million variants of formulas and custom field entries. I keep getting 4.7 (as I believe it sees the 16:30 as 30/100 and not 30/60).

Any ideas? It works fine when the time lapse starts on an even time and ends on a half hour, just not the reverse.

Thank You.

 

[AlphaFrog]

You only applied a custom cell format for display. Your values in F2 and H2 are both still four digit numbers and not serial Time values.

Try this to convert the values to serial time values and calculate the difference. Set the formula cell format to any time format you like.

=TIMEVALUE(TEXT(H2,"00\:00"))-TIMEVALUE(TEXT(F2,"00\:00"))

 

[DRSteele]

Welcome to the forum.

I suspect you would benefit from knowing how Excel Dates and Times work. When you type in 4/27/2018, Excel immediately converts it to Excel Date format, usually the default one on your PC. And when 16:30 is typed in verbatim, Excel immediately converts it to Excel Time format, usually the default one on your PC. In reality, 4/27/2018 is the value 432017; similarly 16:30 is the value 0.6875 What do these values mean. Well, 432017 is the number of days since Excel's declared base year, which is 1/1/1900, or value 1; and 0.6875 is the portion of one day. You can prove this all to yourself by entering 4/27/2018 and 16:30 in two cells and then applying the General number format.

So when you wish to do some time math, your enter (verbatim) 16:30 in one cell (F3) and 21:00 in another (H3) and then in a third (J3) you enter the formula =H3-F3 and you will get 4:30. If you format that cell as General, you will see the result expressed as the value 0.1875

Do you see? Perhaps some youtube videos might clear it all up. Check out this one and also search for countless other related videos in the ExcelIsFun channel. https://www.youtube.com/watch?v=rQh8pqICt5E

 

[cottonangel]

Thank you. This does show me now 4:30 for 4hrs and 30 minutes. However, I have this cell linked to an hourly rate in another field and that is now being thrown off. Is there a formula for converting that hours and minutes into 4.5 for example so I can multiple it times the hourly pay wage?

Welcome to the forum.

So when you wish to do some time math, your enter (verbatim) 16:30 in one cell (F3) and 21:00 in another (H3) and then in a third (J3) you enter the formula =H3-F3 and you will get 4:30. If you format that cell as General, you will see the result expressed as the value 0.1875

 

[DRSteele]

Well, ya. 0.1875 x 24 = 4.5

 

[cottonangel]

Thank you for your kind help. I promise this will be my final question. I have a few entries who cross the midnight mark for time on duty. For example, 20:00 start time to 01:00 end time. In one entry the formula you recommended above worked and calculated 5:00 but in other's it is giving me an error. Thoughts?

 

[DRSteele]

You're welcome. But listen....the best policy is to do time math only when the entire date is there. In other words, a data point like 432017.6875 IS the equivalent of 4/27/2018 16:30. That way, date/time calulations across midnight and across years are never inverted to negatives. Watch some of those videos and it will all become clear as a bell. Roger?