Results 1 to 15 of 17

20050701, 20:21 #1
 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!

20050701, 20:27 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20050701, 20:41 #3
 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)))

20050701, 20:46 #4
 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

20050702, 19:07 #5
 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?

20050704, 14:13 #6
 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?

20050704, 14:22 #7
 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))")

20050704, 14:23 #8
 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

20050704, 14:24 #9
 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

20050704, 14:25 #10
 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

20050704, 14:26 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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>

20050704, 14:26 #12
 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

20050704, 14:27 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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#>.

20050704, 14:53 #14
 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

20050704, 15:03 #15
 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