Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 34

Thread: NUMBER TO TIME CONVERT

  1. #21
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Derek, I'll try it!

    Brian

  2. #22
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Derek,

    When I enter 830, the formula converts to 83:30. Should be 8:30. Tried different formatting but still same results.

  3. #23
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-22 12:42, Brian from Maui wrote:
    Derek,

    When I enter 830, the formula converts to 83:30. Should be 8:30. Tried different formatting but still same results.
    Hi Brian:
    You are getting 83 ... this is the result of extracting two leftmost characters from the string 830 and not 0830. For your formula to work right you have to make sure that 0830 is forced in as text, otherwise it will revert to 830 -- you may force it to enter as '0830 by using an apostrophe as the beginning character. The rest of the stuff work right as discussed in the rest of the thread.
    HTH
    Please post back if it works for you ... otherwise explain it a little further and we will take it from there!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #24
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aloha Yogi,

    Yes it does work with the '.
    Mahalo for your time

    Brian

  5. #25
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good Job Brian!
    Now you can take a coffee break ... or in Maui may be a coconut milk break!

  6. #26
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi,

    One more question (maybe), can the formula be written with the ' already in the formula?

    Brian

  7. #27
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-23 11:15, Brian from Maui wrote:
    Yogi,

    One more question (maybe), can the formula be written with the ' already in the formula?

    Brian
    Hi Brian:
    If you don't want to bother with keying the apostrophe in, before you put the single digit hours like 0830, you must format the cells with FORMAT|CELLS|NUMBER|CUSTOM|@
    This forces the cell to be preformatted as text and will retain the 0 in 0830 and will not strip it.
    HTH
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #28
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi,

    Tried custom @00:00 that doesn't work
    Tried custom '00:00 that works, but only adds the ' to the numbers and still results in 83:30

    Would this work or something like this.

    A macro to insert the : automatically in columns A and B
    Then use the formulas posted in columns C, D, and E?

    Brian






    [ This Message was edited by: Brian from Maui on 2002-03-23 12:02 ]

  9. #29
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Brian:
    Let us say you have two times 0830 and 0600 (notice that these have been entered without the colons). Let us say 0830 will go in cell A2, 0600 will go in cell B2; and C2 will house the time difference between A2 and B2.
    If you want to use the CUSTOM @ format as I suggested. Custom Format the cells A2 and B2 only with @ symbol -- do not combine it with 00:00 format.
    Now with the cells A2 and B2 custom formatted, enter your data in 0830 in A2, and 0600 in B2 (notice no need for apostrophes if the cells are CUstom @ formatted).
    Now in cell C2, write your formula as ...

    =TIME(LEFT(A2,2),RIGHT(A2,2),0)-TIME(LEFT(B2,2),RIGHT(B2,2),0)

    note in this case we don't have to use the 00:0 format at all

    make sure you format the cell C2 as CUSTOM|[h]:mm:ss to see the result as 2:30:00

    or format the cell C2 as GENERAL (or NUMBER, say with 2 decimal places), multiply the result in C2 by 24 to get the result in decimal representation as 2.5 (hours)
    ALOHA!

    _________________
    Yogi Anand
    Edit: Deleted reference to inactive web site from signature line

    [ This Message was edited by: Yogi Anand on 2003-01-19 18:46 ]

  10. #30
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi,

    It worked!!!!
    I just flip flopped b-a part.
    Mahalo for the time and effort

    Brian

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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