# Military Time Elapsed Formula (HELP ME)

1. ## Military Time Elapsed Formula (HELP ME)

I'm looking for a formula to calculate time elapsed when using military time.

Row A B C
1 1800 1854 54
2 1854 1918 24
3 1918 1948 30
4 1948 2030 42
5 2030 2110 40
6 2110 2258 108
7 2258 2323 25
8 2323 2356 33
9 2356 0004 8

I'm about 1,900 rows (that's not an exaggeration) into this document and up until this point I've just been calculating everything mentally as I go but my head might very well explode so a formula would be greatly appreciated. Obviously, I know how to execute simple subtraction formulas but there are instances (i.e. row 4 and 9) where I'm a little out of my depth. I guess I need an IF formula for when B<A and for when B goes into the next hour? Subtract 40 in that case? I don't know. Help me.

Also, like I said, I'm WELL into this document so changing the format from 1800 to 18:00 is just not an option. I know, I should have done that from the beginning and saved myself the misery.

2. ## Re: Military Time Elapsed Formula (HELP ME)

In D1 copied down

=MOD(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"),1)

Format the cells as Custom [mm]
See attached.

3. ## Re: Military Time Elapsed Formula (HELP ME)

If you want this in time format format columns C as Custom "h:mm" and use formula in C1

=REPLACE(B1,3,,":")-REPLACE(A1,3,,":")

 Row\Col A B C 1 1800 1854 0:54 2 1854 1918 0:24 3 1918 1948 0:30

4. ## Re: Military Time Elapsed Formula (HELP ME)

I would go with AlKey's solution.

Optionally: Since time is stored as decimal value with 24 hours being 1.00000, if you need the minutes to be stored as whole numbers, you could multiply the output with 24 and 60:
=( REPLACE(B1,3,,":")-REPLACE(A1,3,,":") ) * 24 * 60

5. ## Re: Military Time Elapsed Formula (HELP ME)

Alkey's solution will not fix the issue when the time goes into the next day. You would need to nest it in the MOD function

=MOD(REPLACE(B1,3,,":")-REPLACE(A1,3,,":"),1)

6. ## Re: Military Time Elapsed Formula (HELP ME)

Thank you ChemistB. Looks like I missed that part. I did look into the adding MOD but results are come out somewhat different

=MOD(REPLACE(B1,3,,":")-REPLACE(A1,3,,":"),1) in column F and modified formula in E

=IF(B1<A1,("24:00"-REPLACE(A1,3,,":"))+SUBSTITUTE(B1/100,".",":"),REPLACE(B1,3,,":")-REPLACE(A1,3,,":"))

 Row\Col A B C D E F 1 1800 1854 54 54 0:54 0:54 2 1854 1918 24 24 0:24 0:24 3 1918 1948 30 30 0:30 0:30 4 1948 2030 42 42 0:42 0:42 5 2030 2110 40 40 0:40 0:40 6 2110 2258 108 108 1:48 1:48 7 2258 2323 25 25 0:25 0:25 8 2323 2356 33 33 0:33 0:33 9 2356 4 8 08 0:08 4:04 10 2356 25 29 0:29 1:04 11 2356 59 63 1:03 11:04

7. ## Re: Military Time Elapsed Formula (HELP ME)

More ways:

 Row\Col A B C D E 1 Start Stop Dec Min Time 2 1800 1854 54 0:54 C2: =MOD(60*(DOLLARDE(B2/100,60)-DOLLARDE(A2/100,60)),1440) 3 1854 1918 24 0:24 D2: =MOD((DOLLARDE(B2/100,60)-DOLLARDE(A2/100,60))/24, 1) 4 1918 1948 30 0:30 5 1948 2030 42 0:42 6 2030 2110 40 0:40 7 2110 2258 108 1:48 8 2258 2323 25 0:25 9 2323 2356 33 0:33 10 2356 4 8 0:08 11 2356 25 29 0:29 12 2356 59 63 1:03

There are currently 1 users browsing this thread. (0 members and 1 guests)