Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting cells as hours, not time in excel (2000)

    Hello,

    I'm sure I've done this before, but it' s been a while and I've forgotten <img src=/S/confused.gif border=0 alt=confused width=15 height=20> .

    I'd like to enter time spent on something into a cell as hours:minutes:seconds, but when I enter this into Excel, it interprets it as a time of day. How can I get Excel to stop thinking that 5:30:24 is 5:30 am and understand that it is actually 5 hours, 30 minutes and 24 seconds of total time?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting cells as hours, not time in excel (2000)

    They are equivalent numbers to excel. you can format the cells as (no quotes):
    "[h]:mm:ss"
    to display (and it will go over 24 hours)

    If you want the DECIMAL hours (5.50667) format as a number and multiply it by 24 (Excel stores all dates and times as "fractions of a day") so you multiply the number of days by 24 to get hours. For minutes mult by 24*60 for seconds 24*60*60, etc

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Formatting cells as hours, not time in excel (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Bob

    How are you entering these numbers, are you using the ":" to separate the digits? What happens if something takes 26 hours, 30 minutes, and 5 seconds, how will you enter this?

    Try one of these:

    Number Format under Time = "h:mm:ss AM/PM"

    or

    Number Format under Time = "h:mm:ss" for what we call Military time, which is based on 24-hours per day, and not AM and PM, so 1:00 PM will be 1300hr. and 1:00 AM will be 0100hr.

    Hope this helps, and I hope I got your question right?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting cells as hours, not time in excel (2000)

    Hello,

    Thanks for the replies, but I'm still stuck.

    The question is based on imported rows that contain tasks that take certain amounts of time to perform. The elapsed time of these tasks is displayed as hours, (sometimes more than 24) and minutes and seconds. So when the numbers come in, if a task takes 34 hours, 6 minutes and 37 seconds to complete, we would like the column to read 34:06:37. Now, the second problem is (and should be solved by the solution to the first) that we would like to compare times to get a difference between actual elapsed time and projected elapsed time. So if the actual elapsed time was 34:06:37 and the projected time was 38:00:00, we would like to come up with a difference of 3:53:23.

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Formatting cells as hours, not time in excel (2000)

    OK Bob

    One thing, earlier you said you are entering these numbers, now its importing?!

    OK what process are you using to import the data, is the data delimited? If so, maybe that is the key. If they are, store them into separate columns, and display the data you want to see in a column by itself.

    So now when you want to do the calculations, you do it on the imported columns, and you display the result into a column.

    It would be a great idea if you could post a sample of the data source, and then also the worksheet and maybe we'll try to make it work for you.

    Now this is what we call "Shooting from the Hip", maybe there is a better solution based on the data source.

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting cells as hours, not time in excel (

    Try looking at this thread. It deals with a number of the issues that you are raising. HTH
    Gre

  7. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting cells as hours, not time in excel (2000)

    Hi Bob

    I think the attached 'appears' to do what you want? All I have done is taken the advice of others and formatted ALL the cells in question to [h]:mm:ss and the math appears to work? Does this help any?

    Regards
    Peter

Posting Permissions

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