how do i change hours and minutes into a decimal number?

[Mills00]

i am trying to turn total flight hrs into a decimal number( ie: 2045hrs
10mins into 2045.10). can anyone help me with a formula which may help in
this? time is given HH:MM but i need it turned to HH.M.

 

[Mike H]

Hi,

The decimal time for 2045h 10m is obtained by multiplying the time by 24 and
formatting as general or a number but this will give an answer of 2045.167
and not 2045.1 because .1 of and hour is 6 minutes not 10.

Mike

 

[MartinW]

Hi Mills,

Do a little play like this

In A1 put 2045:10
In B1 put =A1*24 (this will
return 2045.1666667)
In C1 put =INT(B1) (this will return
2045)
In D1 put =ROUND((B1-INT(B1))*60,0) (this will return 10)
In E1 put = C1&"."D1 (this will return
2045.10 as text)
In F1 put = (C1&"."D1)*1 (this will return
2045.10 as numeric)

Putting all that togethher

=INT(A1*24)&"."&ROUND((B1-INT(A1*24))*60,0)
will return 2045.10 as a text value and

=(INT(A1*24)&"."&ROUND((B1-INT(A1*24))*60,0))*1
will return 2045.10 as a numeric value.

Of course if you are going to twist mathematics like this you
should be very very careful at every step.

P.S. I hope the wrap doesn't play with my formatting here, it's
looking very neat before I send it.

HTH
Martin

 

[MartinW]

Woops, I left the B1 references in there, those formulae
should be,

=INT(A1*24)&"."&ROUND((A1*24-INT(A1*24))*60,0)
and
=(INT(A1*24)&"."&ROUND((A1*24-INT(A1*24))*60,0))*1

Like I said be very careful when you play with fire...........!!?

Regards
Martin