+ Reply to Thread
Results 1 to 7 of 7

Time format to display 12000:00

  1. #1
    Registered User
    Join Date
    07-12-2010
    Location
    Ashfield Pa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Time format to display 12000:00

    Hi,
    This is my first post. I've been working with Excel for a long time and usually can figure out the answer to a problem with the HELP built into Excel but this time I'm hitting a road block. I'm using Excel 2003.
    I'm working on a log book sheet to track daily flight time for a helicopter at work. The time is tracked in hours and minutes. The only format I can find on Format Cell>Tme that will allow a display greater then 23:59 is one listed as 37:30:55. I've been using that format on the programs I've done in the past even though I don't really want to display seconds. Now I'm running into a limitation of the 37:30:55 format when displaying time for a heliocpter with more then 11000 hours on the airframe. If I enter 11000:00:00 in the cell the cell reads 11000:00 but when adding time to that number the SUM cell does not show the proper addition.

    Is there a format that will allow the display 11000 hours as HHHHHH:MM also is there a format that will allow me to display only hours and minutes with out the seconds?

    Thanks for your help.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Time format to display 12000:00

    You can use a custom format of [h]:mm to exclude seconds (the square brackets indicate elapsed time, i.e. possibly over 24 hours).......but Excel doesn't allow you to enter a time value of 10000 hours or higher.....unless it's a calculated value, so as a workaround you can get a valid 11000:00 using some sort of calculation, e.g.

    ="6000:00"+"5000:00"

    format as above
    Audere est facere

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: Time format to display 12000:00

    More info here to confirm DLL quote

    http://support.microsoft.com/kb/214386
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  4. #4
    Registered User
    Join Date
    07-12-2010
    Location
    Ashfield Pa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Time format to display 12000:00

    Thank you for your quick replies. I'll have to work on how to work into the spreadsheet your idea of adding 5000 and 6000 to equal a total of 11000 hours.

    I've looked in Format Cells>Custom and can't find [h]:mm as one of the possible formats. There is a [h]:mm:ss format however. Is there a way add the needed format?

    Thanks again for your help.

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Time format to display 12000:00

    You don't have to select the format from a list - you can type it in

    Format > Number > Custom

    then just type or paste the format you want into the "type" box, i.e.

    [h]:mm

    OK

  6. #6
    Registered User
    Join Date
    07-12-2010
    Location
    Ashfield Pa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Time format to display 12000:00

    Very interesting I did not know you could do that. Wow.
    Thank you for your help.

  7. #7
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: Time format to display 12000:00


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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