Adding Military time formula

**Homeuser66** · 03-10-2007, 12:08 AM

Hi, does anyone know of a formula to put into Excel to add Military time? What I mean by this is, say I start work at 1447 hrs (2:47PM) and work till 2337 (11:37PM). I want to be able to add this to get my total work time. Then say I have to start work again at 0853 the following day. I now also need to be able to tell how many hours and minutes I was off duty?

**Carim** · 03-10-2007, 04:30 AM

Hi,

You can use following formula ...

Please Login or Register  to view this content.

**daddylonglegs** · 03-10-2007, 06:53 AM

Originally Posted by Homeuser66

Hi, does anyone know of a formula to put into Excel to add Military time? What I mean by this is, say I start work at 1447 hrs (2:47PM) and work till 2337 (11:37PM). I want to be able to add this to get my total work time. Then say I have to start work again at 0853 the following day. I now also need to be able to tell how many hours and minutes I was off duty?

For excel to recognise "military" times you need to use a colon.

Assume you enter your start time in A2 as 14:47, end time in B2 as 23:37 and then start time next day in A3 as 08:53

you can get the work time with

=B2-A2+(A2>B2)

format as h:mm

you can use exactly the same formula, with cell references adjusted, to get the difference in time between B2 and A3

=A3-B2+(B2>A3)

note: excel can do this whatever the time format, if you enter the times as 2:47 PM [note the space before the P] or 8:53 AM you can get exactly the same results

**Homeuser66** · 03-12-2007, 08:50 PM

Thank you!! I will try this out...Sorry it took soo long to reply, I reformatted my hard drive and it took a few days to get back up to speed...lol

**Homeuser66** · 03-14-2007, 06:58 PM

Hi, thanks again for helping me with my post. I have another issue I was wondering if you could help?
oK, I got it to do most of what I want it to do..Attached is a picture of what I am doing. I work for the Rail Road and I have this type of form on paper, but I wanted to do it in Excel so it would be easier to add up Military time instead of using the clock and fingers...lol this is similar to a Federal Railrioad Administration-Hours of Service Report.

If you can't see it, I can put my email address here as well...

**Shedezeboo** · 01-14-2014, 04:09 PM

Originally Posted by daddylonglegs

For excel to recognise "military" times you need to use a colon.

Assume you enter your start time in A2 as 14:47, end time in B2 as 23:37 and then start time next day in A3 as 08:53

you can get the work time with

=B2-A2+(A2>B2)

format as h:mm

you can use exactly the same formula, with cell references adjusted, to get the difference in time between B2 and A3

=A3-B2+(B2>A3)

note: excel can do this whatever the time format, if you enter the times as 2:47 PM [note the space before the P] or 8:53 AM you can get exactly the same results

What happens when the time is reversed meaning B2 6:45 and A2 is 6:50. How do you calculate the time between the two cells if the time has been moved up?

**FDibbins** · 01-14-2014, 04:14 PM

Hi shedezboo and welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**Homeuser66** · 01-20-2014, 03:04 PM

Originally Posted by Shedezeboo

What happens when the time is reversed meaning B2 6:45 and A2 is 6:50. How do you calculate the time between the two cells if the time has been moved up?

Hi, I didn't mind that you posted here again. I haven't looked at or even used Excel for this in 7 yrs...maybe PM daddylonglegs himself....