Microsoft KB Archive/60473

{| = Excel: Multiply Time Entry to Obtain Decimal Number Equivalent =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q60473

SUMMARY
When performing operations on times in Microsoft Excel, multiply the figures by the appropriate amount to obtain decimal values for hours, minutes, or seconds. To obtain hours, multiply the time result by 24; for minutes, by 1440; for seconds, by 86,400.

MORE INFORMATION
Excel handles times as fractions of days in order to facilitate date/time calculation. Numbers entered directly as times (for example, &quot;9:30 AM&quot;) are calculated as decimal fractions of 1. Problems may arise when these figures are used in calculations. For example:


 * 1) Enter &quot;9:30 AM&quot; (without quotation marks) in cell A1.
 * 2) Enter &quot;10:35 AM&quot; (without quotation marks) in cell A2.
 * 3) Enter &quot;=A2-A1&quot; (without quotation marks) in cell A3; the number displayed will be 0.04513889.
 * 4) Format the cell as &quot;h:mm&quot; by choosing Number from the Format menu and selecting &quot;h:mm&quot; from the list. The result will then be displayed as 1:05. However, multiplying this by an hourly pay rate will give unexpected results, since Excel is storing the value as a fraction of a day.
 * 5) Change the formula in cell A3 to &quot;=(A2-A1)*24&quot; (without quotation marks) and format the cell as &quot;General&quot; by choosing Number from the Format menu and selecting &quot;General&quot; from the list. The number will be displayed as 1.083333; this reflects the accurate figure in decimal hours.
 * 6) Enter the formula &quot;=(A2-A1)*1440&quot; (without quotation marks) in cell A4. The result, formatted as &quot;General&quot;, will be 65 (minutes).
 * 7) Enter the formula &quot;=(A2-A1)*86400&quot; (without quotation marks) in cell A5. The result, formatted as &quot;General&quot;, will be 3900 (seconds).

To restore the figures to an actual time value, divide them by the appropriate increment. Note that times are handled as time since January 1, 1904, (the first date of the Macintosh internal clock); mixing times and actual dates requires handling them accordingly.
 * }