Convert mm.ss to hh:mm:ss

Hi all - newbie question here.

I've taken on an old excel datasource any I need to restructure it and a column is foxing me.

The column lists time in mm.ss format (decimal seperator), so 1.5 hours is shown as 90.00. For future use, I need this column to be formatted in hh:mm:ss (colon sepertors), ie 1:30:00

If I simply change the cell format to hh:mm:ss, it goes

Any ideas, pointers would be most appreciated.

Originally Posted by BigJohnson

If I simply change the cell format to hh:mm:ss, it goes

that is the best ways to change, I think so
does it work correctly

hi
I see what your doing with the divide by 1440 etc, but the 1440 doesn't calc.

eg. 76.54 / 1440 & formatted to hh:mm:ss calcs as : 1:16:32, when it should in fact be 1:16:54

....another way

=TEXT(A1*100,"00\:00")/60

that's perfect TigerTiger - a star - cheers !

however, if yr data is a total, that is may lead to a prolem
for example: A1=11174.34 the result will not correct
what about your case?

Originally Posted by tigertiger

however, if yr data is a total, that is may lead to a prolem
for example: A1=11174.34 the result will not correct
what about your case?

Hello tigertiger, are you referring to the solution I posted? That formula will cope with minutes < 9999, your formula will cope with minutes < 1440.

for minutes > 9999 try

=(A1+MOD(A1,1)*2/3)/1440

format result cell as [h]:mm:ss

Originally Posted by daddylonglegs

Hello tigertiger, are you referring to the solution I posted?

NO, I just give BigJohnson a mention that is a limitation of my solution.