NUMBER TO TIME CONVERT - Page 2
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 34

Thread: NUMBER TO TIME CONVERT

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

    Default

     
    CJ

    Learned this from this board. Only you won't be able to add/subtract time.

    Format/Cells/Custom
    00:00

  2. #12
    Board Regular CJ's Avatar
    Join Date
    Feb 2002
    Location
    CHESTER, ENGLAND
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank Brain

    Tried what you mentioned but it won't accept it as a format!!!

    CHRIS

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

    Default

    Chris,

    I just tried it, it works. I'm using Excel 97. Highlight the cells, then format..etc.

    Brian

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

    Default

    CJ

    Should have only one back slash

    00:00

    Brian

  5. #15
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Alternatively, using Brian's method, but instead of "00:00", use "hh:mm"

  6. #16
    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-21 17:30, BabyTiger wrote:
    Alternatively, using Brian's method, but instead of "00:00", use "hh:mm"
    Hi Baby Tiger:
    I was trying to follow what you meant, but I am not getting what tou meant. Brian's FORMAT|CELLS|NUMBER..."Custom 00:00" sans quotes works for me!

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

    Default

    Using the format I gave, I'm trying to subtract the time.

    11:59-13:00=61Mins.
    Answer I get is 1:41
    Anyone have any ideas?

    Mahalo...Brian

  8. #18
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Brian
    You could use an if statement that goes along the lines if A>=B then A-B else B-A.
    regards
    Derek

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

    Default

    Derek,

    In my situation, A1 will always be less than B1 as I'm using a 24 hour format unless we past midnight, but never so far. I got his custom format because I didn't want to type 12:00 but 1200 with the colon inserted by the custom format. But I can't get it to subtract properly. i.e. 11:59 and 13:00 to :61.

  10. #20
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Brian
    That's because the format you are using gives the appearance of time but the colon actually acts as a decimal point, so you are subtracting decimals not time.
    I had a similar problem where I needed to enter 4 digit time in a time calculator.
    I overcame it using hidden columns that were formatted [h]:mm
    Try this:
    Custom Format A1:B1 as 0000
    Custom Format C1:E1 as [h]:mm
    in A1 type 1159
    in B1 type 1300
    in C1 put formula =LEFT(A1,2)&":"&RIGHT(A1,2)
    In D1 put formula =LEFT(B1,2)&":"&RIGHT(B1,2)
    In E1 put formula =D1-C1
    You can then hide columns C:D
    Since time format won't allow negative time, you may have to revert to using the if function to take the smaller time from the larger, if this is an issue.

    hope this helps
    regards
    Derek

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
  •  

 

 
DMCA.com