Format decimal to specific type of format

Hi there,

I am trying to convert a number which shows decimal time formatting to a time formatting however to be displayed again as decimal. e.g


Cell A2 = 24.25 (which is 24hours and 15minutes)
Cell B2 should be equal to 24.15 (which is 24 hours and 15minutes)

I tried to use the timevalue formula but is not displaying the format that I want and they all seem to stop after 24hours (displaying 0 values)


Can anyone help please??

Not clear if you want B2 to be 24.15 or 24:15 format as 24.15 - the two are very different values.

For 24.15 as Decimal

=DOLLARFR(A2,60)

(DOLLARFR requires activation of Analysis ToolPak pre XL2007)

or

=INT(A2)+MOD(A2,1)*0.6

If you want B2 as hours but format as though decimal then:

=A2/24
format as [hh].mm

One option:

=ROUNDDOWN(A2,0)+MOD(A2,SIGN(A2))*0.6

assumes A2 not 0

Thank you very much DonckeyOte, you are a master.Do you mind writing the formula for the reverse option please? So I can allow the user if they only have the time e.g
D2= 5.30 (time)
E2= 5.50 (decimal) including negative values

Currently I am using

=IF(ISERROR(RIGHT(D2,LEN(D2)-FIND(".",D2)+1)),D2,LEFT(D2,FIND(".",D2)-1)+(ROUNDDOWN((RIGHT(D2,LEN(D2)-FIND(".",D2)+1)/0.6),2)))

which doesnt allow negative time.

If it is too much trouble not to worry.

Many Thanks

To work in reverse:

=ROUNDDOWN(A2,0)+MOD(A2,SIGN(A2))/0.6

(above assumes A2 not 0 obviously)