Expert: Douglas M. Smith - 10/3/2009

Question
Hi there:  I am trying to find the correct formula to convert a time, for example 9:00 am, to simply the number 9.00 then deduct that 9.00 from 90. to get a final result of 81.  Another example would be 6:40 am converted to 6.40 minus 90 = 83.60  The purpose is to get a directional bearing which is not important but i can't figure out a formula.   Thanks.

Answer
Hi Bruce,

Sorry for the delay, I've battling the flu.

Time values in Excel are really just the decimal part of a day.  Examples: 6:00 am is 0.25, noon is 0.5.  To convert a time to the decimal number of hours, multiply the time by 24.  Be sure to also change the cell format for a time format to number format.

It gets trickier to strip out the minutes and make it into a decimal amount.  For a time in cell A1, the formula would be:

=INT(A1*24)+((A1*24)-INT(A1*24))*60/100

The first part of the formula gets the hours and the second get the minutes amount and multiplies by 60 minutes/hour and then divides it by 100 to get the decimal, which is then added to the hour amount.  This can now be treated like any other number:

=90-INT(A1*24)+((A1*24)-INT(A1*24))*60/100

Doug Smith
Brainbench MVP for MS Excel
www.brainbench.com
www.abundant-solutions.com