Convert existing numbers to time

[trekcowboy]

I have a column of numbers such as 123, 245, 345 etc.

I would like to convert these to times such as 1:23, 2:45, and 3:45, when I choose format cells I get a value of 0:00

Thanks for your help in advance.

 

[Mark W.]

If these values are 24-hour representation of hours and minutes use...

=SUM(MID(REPT(0,4-LEN(A1))&A1,{1,3},2)/{24,1440})

...where A1 contains your value. Format the cell containing this formula as h:mm

 

[trekcowboy]

EXCELLENT Thank you!

I hate to ask this but could you please tell me how to include seconds.

24511 = 2:45:11

Thanks again

 

[Mark W.]

On 2002-03-06 11:01, trekcowboy wrote:
EXCELLENT Thank you!

I hate to ask this but could you please tell me how to include seconds.

24511 = 2:45:11

Thanks again

=SUM(MID(REPT(0,6-LEN(A1))&A1,{1,3,5},2)/{24,1440,86400})