Decimal to time using number formatting only?

[David Norman]

I have a decimal values representing times in a pivot table e.g.

3.5 = 3 minutes 30 seconds
4.75 = 4 minutes 45 seconds

I want these to be displayed as 3:30 and 4:45, totalling to 8:15. Since this
is pivot table data I can only use formatting, not formulae. Can it be done?

Thanks in advance
David

 

[Roger Govier]

Hi David
You would need to have the formatting as mm:ss in your source table, then
you could also set the format as mm:ss in the PT and see the results you
want.

You could add an additional column to your source table with the formula
=A1/1440
where a1 represents your decimal time, and format the column as mm:ss
Excel stores time as fractions of a day hence dividing by (60*24) 1440 gives
you a result in minutes and seconds

Use this new column of times in the PT, rather than the original.