NUMBER TO TIME CONVERT
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 34

Thread: NUMBER TO TIME CONVERT

  1. #1
    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

     
    Hi guys, A nice simple one!!!!

    I have imported some data into a spreadsheet
    but the time has come across as a number
    ie, 1630 instead of 16:30 (to many to do manually - 500+)

    Can anyone advise me on a formula or a custom format to insert a colon in the number two places in from the right

    CHRIS

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

    Default

    Hi,

    If you are looking for an Excel formula, then you mgiht want to try the following:

    =TIME(LEFT(E15,2),RIGHT(E15,2),0)

    If you are looking for an macro formula/code, then the following might help

    Range("A1").Value = TimeSerial(Left(Range("E15").Value, 2), Right(Range("E15").Value, 2), 0)

    Hope it helps.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-21 13:08, CJ wrote:
    Hi guys, A nice simple one!!!!

    I have imported some data into a spreadsheet
    but the time has come across as a number
    ie, 1630 instead of 16:30 (to many to do manually - 500+)

    Can anyone advise me on a formula or a custom format to insert a colon in the number two places in from the right

    CHRIS
    In B1 enter and copy down:

    =(LEFT(REPT("0",4-LEN(A1))&A1,2)&":"&RIGHT(A1,2))+0

    where A1 houses the first target value.




  4. #4
    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

    Hi Baby Tiger,

    Brillant that's just what I was looking for,
    one other little thing I would like the time to show in a 24 hr clock format could this be done in the formula,I know that I could reformat the the cells to a [h]:mm format but would like if poss to do this in the formula!!!

    CHRIS

  5. #5
    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 13:25, Aladin Akyurek wrote:
    On 2002-03-21 13:08, CJ wrote:
    Hi guys, A nice simple one!!!!

    I have imported some data into a spreadsheet
    but the time has come across as a number
    ie, 1630 instead of 16:30 (to many to do manually - 500+)

    Hi CJ:
    as long as both the hours and the minutes are in two digits, you can use the following formula to convert 1650 to 16:45

    =LEFT(C31,2)&":"&RIGHT(C31,2)
    in this case 1650 is housed in cell C31


    Can anyone advise me on a formula or a custom format to insert a colon in the number two places in from the right

    CHRIS
    In B1 enter and copy down:

    =(LEFT(REPT("0",4-LEN(A1))&A1,2)&":"&RIGHT(A1,2))+0

    where A1 houses the first target value.



    Regards!

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

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

    Default

    sorry guys, above post got all messed up

    Chris: If both the hours and the minutes are in two digits each, and say 1650 is in cell C31, then to convert 1650 into 16:50, use the following formula:

    =left(c31,2)&":"&right(c31,2)

    HTH

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-21 13:44, Yogi Anand wrote:
    sorry guys, above post got all messed up

    Chris: If both the hours and the minutes are in two digits each, and say 1650 is in cell C31, then to convert 1650 into 16:50, use the following formula:

    =left(c31,2)&":"&right(c31,2)

    HTH
    Try the formula I proposed: It should also work for, say:

    445
    20
    00

    Aladin



    [ This Message was edited by: Aladin Akyurek on 2002-03-21 14:15 ]

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

    Default

    Hi CJ,

    To format the time to 24hrs format, in Excel:

    =text(time(),"hh:mm")

    In macro:

    =format(time(),"hh:mm")

    Then it should be 24 hrs.

  9. #9
    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 14:06, BabyTiger wrote:
    Hi CJ,

    To format the time to 24hrs format, in Excel:

    =text(time(),"hh:mm")

    In macro:

    =format(time(),"hh:mm")

    Then it should be 24 hrs.
    Just to elaborate what BabyTiger said about the formula ...
    if the time was shown as 1650 as in your example, you will have to parse 1650 into hours (16), minutes (50), and seconds (0) before you can use this in the TIME function ... so Baby Tiger's formula expanded will look like (if 1650 were in cell A1)...

    =TEXT(TIME(left(A1,2),right(A1,2),0),"hh:mm")


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

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

  10. #10
    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

      
    Thanks Yogi,

    I was just going to reply to TB's post to ask where: =text(time(),"hh:mm") went in the formula, then I saw your reply. I now understand what TB meant.

    =text(time(TB ORIG FORMULA),"hh:mm")

    I'm learning slowly!!

    Thanks to everyone who replied to my post

    You have all saved me alot of typing

    CHRIS


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