Excel: How to Change Decimal Elapsed Time to Mins & Secs 2

[bsquared18]

Hi,

I'm working on an Excel worksheet where one of the values is an elased time. Let's say, for example, that Excel calculates an elapsed time of 5.25 minutes. Naturally, that translates into 5 minutes 15 seconds. Is there a way in Excel to display the decimal elapsed time as minutes and seconds?

Thanks!

Bill

 

[macropod]

Hi bsquared,

For a value in A1, is:
=INT(A1)&":"&MOD(A1,1)*60
as a text result, or:
=TIMEVALUE(INT(A1)&":"&MOD(A1,1)*60)
, with a time format suitable?

Cheers

 

[GlennUK]

This formula will convert decimal minutes to a timevalue:

=A1/(24*60)

have the cell formatted with a suitable time format.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[DPlank]

Wouldn't it be easiest to set the cell format of the cell with the value in it to Custom and specify [h]:mm:ss?

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[GlennUK]

Yes Dave, but only once the value has been converted from decimal.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[bsquared18]

Thanks everyone for your help!

Macropod and Glenn, both your solutions work.

Dave, when I tried your solution, 8.28 minutes became 198:48:00 instead of the desired 8:17.

Bill