Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    converting number to time (excel)

    i have imported a bunch of data into a spreadsheet and the data that is Time is now imported as numbers like

    749, 1046. really these should be 7mins and 49 secs and 10min and 46 sec's and so on.

    I have highlighted all my time data, and formated the cells with the time customizations, and my own personal customize. But have no luck, the data just changes into 00:00 for mm:ss

    I know that I am forgetting something!

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

    Re: converting number to time (excel)

    Say that A1 contains 749. In B1, enter the formula

    =TIME(0,INT(A1/100),MOD(A1,100))

    and format B1 with the appropriate time format. Fill down as far as needed.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting number to time (excel)

    cool thanks,

    if the value its converting has no time can i do something like this:
    =TIME(0,INT(A1/100),MOD(A1,100))="","",INDIRECT(TIME(0,INT(A1/100),MOD(A1,100)))

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting number to time (excel)

    i tried reading help to figure it out, lol have really no idea why indirect is in it, just though I was close to making it work.

    thank you

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting number to time (excel)

    Another option,

    =TEXT(A1,"00:00")+0

    And format as time.

    Are these elapsed times? Is there any time you would want 7:49 PM or 19:49?

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting number to time (excel)

    I have tried this time conversion and get a error message saying "you have entered too few arguments.

    am I missing a bracket?

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting number to time (excel)

    try:

    =IF(A1="","","Time(0,INT(A1/100))")

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

    Re: converting number to time (excel)

    That would put the text string:

    Time(0,INT(A5/100))

    not the converted time into the cell. I don't think that is what was wanted.
    Legare Coleman

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

    Re: converting number to time (excel)

    I think that Hans' formula should have been:

    <pre>=IF(A1="","",TIME(0,INT(A1/100),MOD(A1,100)))
    </pre>

    Legare Coleman

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

    Re: converting number to time (excel)

    Correct, I have edited my most to add the last parenthesis. Thanks.
    Legare Coleman

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

    Re: converting number to time (excel)

    No, sorry, I forgot part of the formula while copying. Try

    <code>=IF(A1="","",TIME(0,INT(A1/100),MOD(A1,100)))</code>

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting number to time (excel)

    =IF(A1="","",TIME(0,INT(A1/100),MOD(A1,100)))
    then format cells, custom format mm:ss

    Sean

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

    Re: converting number to time (excel)

    I'm not sure what your formula tries to accomplish - why is there an INDIRECT in it? Does this work for you?

    <code>=IF(A1="","",TIME(0,INT(A1/100))</code>

    Added: formula incorrect, see below, for example <post#=495352>post 495352</post#>.

  14. #14
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: converting number to time (excel)

    I am wondering if it is possble to build this into the cell where the data is already instead of formating it in a different cell? so instead of clickin on cell b2 and making it equal to a1, cell a1 would be automatically updated to the new format?

    Sean

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

    Re: converting number to time (excel)

    You can copy the cell with the formula, and then use "Paste Special" and in the dialog box select values to paste the new time value back into the original cell. Then the cell with the formula can be deleted. You could also write VBA code that would convert the cell to a time value without using the formula in another cell.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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