Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Bar vs. Number Format (Excel 2000, SR1)

    Hi Folks,

    I extract a time-stamp from a text file and stick it in the worksheet, then apply a custom number format to match the format that was in the original text file. The custom format is:

    <pre>yyyy-mm-dd hh:mm:ss.00
    </pre>


    The value as displayed in the cell itself is exactly as it was in the original text file, down to the 100ths of seconds. However, the formula bar rounds it up or down to the nearest second. See picture. If I click in the formula bar and press backspace, I am erasing a '5' that was rounded up from '4' because of the '.55', rather than erasing the '5' off the back of '.55'.

    Math on these values works out OK, for example, A2-A1 gives you 13:33.07. That is, the 100ths are recognized in the calculations, but not in the formula bar.

    This strikes me as very weird and I am annoyed. Any comments? Thanks!
    Attached Images Attached Images

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

    Re: Formula Bar vs. Number Format (Excel 2000, SR1)

    I think that what gets displayed in the formula bar for a date or time is whatever format is specified in the Control Panel Regional Settings. That format does not appear to have any provision for anything less than a second.
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formula Bar vs. Number Format (Excel 2000, SR1)

    Legare's right, and the only way I can see around this problem something like this, which may not be correct and I'm stumped figuring out how to calculate 100th's of seconds and prove that the calculation is correct!

    =TEXT(NOW(),"mm/dd/yy h:mm:ss")&":"&TEXT(MOD(MOD(NOW(),1)*86400,1),"00")

    (86400 should be seconds in a day)

    I'm not sure if you gain anything by attempting to get time down to 100th's of seconds, as time in a spreadsheet cell doesn't update until there is a change in any cell contents.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Bar vs. Number Format (Excel 2000, SR1)

    Thanks to both for responding. It's less of a problem than a mystery. The value in the cell is indeed retained to the 100th of a second. It is not critical for me to do calculations using the 100ths, but it is a lot easier to find the exact line in the original file if the 100ths are retained.

    As for calculations, I just use seconds, no fractional seconds.

    Aside, the formula I used to verify that the 100ths are indeed retained is:

    <pre>=A2-A1
    </pre>


    with a custom number format as follows:

    <pre>[hh]:mm:ss.00
    </pre>


    The displayed result from the values in the picture is: 00:13:33.07.

    Thanks again.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formula Bar vs. Number Format (Excel 2000, SR1)

    I'm confused, Jim. You are certainly correct that fractional seconds are retained in Excel, as can be demonstrated by taking a cell containing =NOW() and formatting it as a number displaying 10 decimal places. But in XL97 to display 100ths of seconds via "h:mm:ss:00" isn't possible because "h:mm:ss:00" is not a valid custom format, the trailing zeros aren't accepted. Hence my workaround solution using the text functions. Which XL version are you using?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Bar vs. Number Format (Excel 2000, SR1)

    I'm using Excel 2000. I don't know if the custom format is invalid in 97, I would have to double check at home. I noticed in your post, you put ":" between the ss and 00, is this what you meant? See picture showing the format and results.
    Attached Images Attached Images

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formula Bar vs. Number Format (Excel 2000, SR1)

    Using "." (period) instead of ":" (colon) blew right by me on every post, and it -is- a valid format in Excel 97. Which also means that I completely failed to answer your actual original question at every post. <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Thanks, Jim, this has been a learning experience for me, if not for you. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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