+ Reply to Thread
Results 1 to 5 of 5

Converting military time to a number

  1. #1
    Registered User
    Join Date
    03-31-2010
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    8

    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?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    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.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    03-31-2010
    Location
    Texas, US
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Converting military time to a number

    Yes, works now.

    Thank you so much!

  5. #5
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Converting military time to a number

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

    Pauley

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1