Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Mar 2005
    Location
    Huntsville, Alabama, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time Conversion (2002)

    I am going <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> over this one. I am trying to <img src=/S/compute.gif border=0 alt=compute width=40 height=20> create a formula that will help convert seconds into an expression in: hh:mm:ss.000000. This result must be compared to another result in similar format, to calculate the difference in time between the two values. <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45> Big Time!!!

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

    Re: Time Conversion (2002)

    Are the seconds entered as an Excel time value or just as a number of seconds?
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Mar 2005
    Location
    Huntsville, Alabama, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Conversion (2002)

    Sorry about the delay in answering you. I tried to format the cell for time format that would include millesimal value after the second. But Excel will not even interpret decimal nor centesimal values. The format I am trying to create would look like this: "hh:mm:ss.234567." This expression needs to be able to react to other cell values for the purpose of adding or substracting from other values.

    For example, if I want to substract UTC Time from a Simulation Time; these two values are never the same. I need to be able to take two given time values, as expressed above, and substract or add when needed, keeping the same format indicated. I observed some formulas created and presented on this site dealing with hh:mm, and mm:ss; but none with hh:mm:ss. My interest goes a little further: hh:mm:ss.123456; my calculations have to be accurate to that level of expression.

    Thanks again for looking into this for me.

  4. #4
    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: Time Conversion (2002)

    I don't think excel can keep track of time to the nanosecond (s.000000). I think it is limited to the millisecond (s.000) [at least it can not in XL97]

    Steve

  5. #5
    New Lounger
    Join Date
    Mar 2005
    Location
    Huntsville, Alabama, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Conversion (2002)

    I would be willing to live with a rounded value of ss.000. When I tried that in Excel, it would not accept the third argument (ss.000) to complete the full operation. I could only enter ss without any decimal. I dropped the decimals to two digits, then it would not express the decimal value into the next cell.

  6. #6
    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: Time Conversion (2002)

    I have formatted it here. Press <f9> to change the time.

    After looking at this, I do not think excel even does milliseconds. I could not get anything other than a zero in that place, so you might be stuck with s.00.

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time Conversion (2002)

    That is still the same in Excel 2002: NOW() returns the time to 1/100 second.

    Another problem is that Excel will let you enter a time with decimals after the seconds part, and display them correctly, but you can't edit the decimals afterwards. Whether you edit the value in the formula bar or in the cell itself, the decimals are not displayed.

  8. #8
    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: Time Conversion (2002)

    If you enter a formula to convert a cell from seconds to days (eg by dividing by 86,400) and then format to display with "s.000" you can display the milliseconds and it is rounded correctly (at least in the dataset I played with).

    XL97 (I can't test other versions) will not accept anything more than the milliseconds in the format. If you try to add more you get an error message.

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time Conversion (2002)

    Same in Excel 2002: you can define a time format with up to 3 decimal places for the seconds, any more causes an error message when you try to set the format.

  10. #10
    New Lounger
    Join Date
    Mar 2005
    Location
    Huntsville, Alabama, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Conversion (2002)

    Steve:

    Thank you very much. I will try it at the office tomorrow in my spreadsheet. I really apperciate the help. I will keep you posted of the results.

    Serge

  11. #11
    New Lounger
    Join Date
    Mar 2005
    Location
    Huntsville, Alabama, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Conversion (2002)

    Hans:

    Thanks for the tip. I am going to experiment with it tomororrow.

    Serge

Posting Permissions

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