Military Time Elapsed Formula (HELP ME)

**jacquelineelaine** · 12-05-2014, 01:54 PM

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.

Thanks in advance!

**ChemistB** · 12-05-2014, 02:05 PM

In D1 copied down

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

Format the cells as Custom [mm]
See attached.

**AlKey** · 12-05-2014, 02:11 PM

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

**dgranneman** · 12-05-2014, 02:18 PM

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

**ChemistB** · 12-05-2014, 03:04 PM

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)

**AlKey** · 12-05-2014, 03:31 PM

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

**shg** · 12-05-2014, 04:00 PM

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

Military Time Elapsed Formula (HELP ME)

LinkBack

Thread Tools

Rate This Thread

Display

Military Time Elapsed Formula (HELP ME)

Re: Military Time Elapsed Formula (HELP ME)

Re: Military Time Elapsed Formula (HELP ME)

Re: Military Time Elapsed Formula (HELP ME)

Re: Military Time Elapsed Formula (HELP ME)

Re: Military Time Elapsed Formula (HELP ME)

Re: Military Time Elapsed Formula (HELP ME)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Dividing af formula for elapsed time with a fixed(static) time

Simple way to convert military time to standard where military has no colon

Trying to understand a formula to calculate elapsed time in a specific time period

Need Formula Help ~ Converting Military Time in Time Card

time elapsed formula

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions