# Converting military time to a number

1. ## Converting military time to a number

Good afternoon,

I have spent the better part of this afternoon searching this forum and internet to try and solve a problem I am having with a time sheet in excel. I'm hoping someone can see what I can't.

I've attached a test time sheet for reference.

Basically this time sheet calculates hours work daily and then calculates a weekly total. The person inputs that time in this format: HH:MM AM/PM.

The daily and weekly totals are in his format: H:MM (military time).

Then at the bottom of the spreadsheet I'm trying to calculate the cumulative totals from the weeks. Right now the issue I'm having is after 24 hours or above. Like in the test spreadsheet the true total I want to be 25 not 1:00. How do I convert the cumulative total to a number? I've tried formatting the cell, but I'm not getting the right results.

Any ideas?

2. ## Re: Converting military time to a number

A few problems. First, you have to realize that if a cell is formatted as time, it will only show you the time, not the days.
For example, in cell F9, your formula shows '6:00', but the number (due to your formula) is actually calculating to be 1 day plus 6:00. The 1 day is hidden since you are not asking it to show the day.

To see this, change you cells to 'General' instead of the Time format. In cell F9, you will see that it is 1.25, which is 1 and a quarter days. You really just want 0.25. This is important when you sum it up, since you are adding extra days. Suggest you change the formula in F9 to =E9-B9.

Since you have the cell formatted as Time, it cannot show 25:00, since that is not a valid time. So you need to treat it as a number. A simple solution is to change that cell (F27) to a 'General' format and then use =24*sum(F12+F19+F26). You multiply by 24, since a '1' in time format is 1 day, which is 24 hours. Note that this will give you a format of '25' not '25:00', or in the case of fractions, '25.5' and not '25:30'. If you want to also convert 25.5 to 25:30, let me know, or you may be able to figure that out yourself.

3. ## Re: Converting military time to a number

Since you have the cell formatted as Time, it cannot show 25:00, since that is not a valid time.
It can if you format as [h]:mm

4. ## Re: Converting military time to a number

Yes, works now.

Thank you so much!

5. ## Re: Converting military time to a number

Cool shg. Learn something new every day. Gotta love the Texans helping each other out.

Pauley

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