Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Minutes & Seconds in Excel

    How do you format a cell so that the time you enter in reflected as hours and seconds?

    I've tried by formatting the cell itself by picking hh:mm from the special category in the formatting section. However, whenever we enter in a time "08:24" for 8 min 24 secs, it always assumes it is 08:24 AM.

    When I enter it in the long way (i.e. 00:08:24) and click on that cell, it says 12:08:24 AM. Any way to JUST show the minutes and seconds? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    THANKS!

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Minutes & Seconds in Excel

    Try: format >> Cells >> Custom and choose either [u]:mm:ss or mm:ss and enter your data as e.g. 00:08:24

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Minutes & Seconds in Excel

    You will need to create a custom format [mm]:ss. The brackets are typed into the format.

    You will have to enter the hours, even it it is zero. If you enter 8:24, Excel will take that to be 8 hours 24 minutes.
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Minutes & Seconds in Excel

    Hans: On what version of Excel does [u]:mm:ss work? My Excel 97 rejects that with a message saying that it could use the number format specified.

    Also, the format mm:ss will only work for times less than 1 hour. If you enter 1:08:24, that format will display 8:24, not 68:24.
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Minutes & Seconds in Excel

    Sorry, this is Excel 2000. I am also sometimes confused with languages. I am using an English version of Excel but my Windows settings are Belgian. Could it be that you need to change [u] to [h]?
    This formatting will never display times like 68:24, but rather 01:08:24. The mm:ss format indeed ignores the hours. The best choice is your suggestion: [mm]:ss.

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Minutes & Seconds in Excel

    If you use the custom format '00":"00' you can enter the time of 11 minutes, 20 seconds as 1120 and it will display correctly. This avoids you having to always key a leading '0:' for the hours, but involves a bit more work if you need to sum the minutes and seconds.

Posting Permissions

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