Military Time in Excel

1 Oct

I was working with military time in Excel and needed to calculate the hours:minutes:seconds between two times. The answer seemed simple – subtract the end time from the start time (1:30 to 2:00 is 2:00-1:30= 00:30.). This worked except when the start time was at around 23:45 and the end time was the next day at 00:34.  Here is my solution:

Column B (Start Time)                                  Column C (End Time)

23:51                                                                                00:01

=if( C1>B1, C1-B1, (24-B1)+C1) will give you 00:10

What the formula does is determine if it can just subtract the end from the start. If the start time is greater than the end time (the time ends the next day), the formula subtracts the end time from 24 to get the remaining time left in the day then adds the time from the next day. So in the example 24-23:51 = 9 then add 9 + 00:01 = 00:10.

Not exactly rocket science, but it works. A lot of the answers I found online suggest converting to other formats then back to military time and blah blah blah. Too much work.  I need a simple solution and found one. So far it has worked.

Advertisements

2 Responses to “Military Time in Excel”

  1. Ron Abraham October 3, 2013 at 4:53 am #

    I am not getting the same answer. For example E=22:12 and F=01:12 – I am getting 555.00 hours.

    • paulcrickard October 3, 2013 at 2:41 pm #

      I used 22:12 and 1:12 and got 23.125. When I formatted the cells to time, it said 3:00. That is correct. Your answer makes me think it’s the formatting in excel. Make sure the cells are formatted CUSTOM-h:mm

      Excel is great at trying to guess what format you want and messing it up.

      Hope that helps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: