Solved Converting military time to hours/minutes in Excel

March 8, 2013 at 10:41:19
Specs: Windows 7

I have tried using a simple subtraction equation to determine the difference in hours using military time, however when you get to half hour increments, for example, 1700 - 1230, You see the minutes as 70, which is the correct subtraction. I need to know how to convert the 100 units to 60 showing 5 hours and 30 minutes, not 4 hours and 70 minutes. I thought if inserting an if statement to add 160 units, but can't remember the condition "ends with" 70. Thank you!.

See More: Converting military time to hours/minutes in Excel

Report •


#1
March 8, 2013 at 11:57:09
✔ Best Answer

It is probably happening because you do not have the cells formatted as TIME.

In cell A1 enter 17:00 << NOTE the colon - it is REQUIRED
In cell B1 enter 12:30 << NOTE the colon - it is REQUIRED

In cell C1 enter the formula =A1-B1
you should now have an answer of 4:30

MIKE

http://www.skeptic.com/


Report •

#2
March 8, 2013 at 15:11:09

Don't start a new thread for the same question. I have deleted your other post as you have a perfectly good answer here

If you need clarification, post in this thread.

Stuart


Report •

#3
March 9, 2013 at 02:06:49

... and this thread should be posted here: http://www.computing.net/forum/offi...

Report •

Related Solutions

#4
March 15, 2013 at 11:53:40

Thank you for the response. I did try format the cells to time, but when I put in the data, it comes back as 00:00 PM. I'm not sure what I am doing wrong, but I did find a formula that worked:
=(60 * (DOLLARDE(B3/100, 60) - DOLLARDE(B2/100, 60) + 24 * (B3+0 < B3+0) ))

Again, thank you Mike!


Report •

#5
March 15, 2013 at 12:01:25

Are you going past Midnight with your calculations?
Did you enter the time with or without the AM/PM?

Here is a great source for Date & Time info:

http://www.cpearson.com/excel/datet...

MIKE

http://www.skeptic.com/


Report •


Ask Question