AboutDouglas M. Smith Expertise I specialize in solving formula, feature and structure related problems. I know many tricks to help make your spreadsheets and processing more efficient.
Experience Brainbench MVP for MS Excel
Past/Present clients Gannett, Fannie Mae, Pepsi, Nortel, Procter & Gamble, BellSouth, Blue Cross Blue Shield of NC, NC Central University, GlaxoSmithKline, Maintenance Excellence Institute, AAI Pharmaceuticals, Blue Cross Blue Shield of SC, Brainbench.com
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
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
