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,
"9:30 AM") are calculated as decimal fractions of 1. Problems may
arise when these figures are used in calculations. For example:
- Enter "9:30 AM" (without quotation marks) in cell A1.
- Enter "10:35 AM" (without quotation marks) in cell A2.
- Enter "=A2-A1" (without quotation marks) in cell A3; the number
displayed will be 0.04513889.
- Format the cell as "h:mm" by choosing Number from the Format menu
and selecting "h:mm" 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.
- Change the formula in cell A3 to "=(A2-A1)*24" (without quotation
marks) and format the cell as "General" by choosing Number from the
Format menu and selecting "General" from the list. The number will
be displayed as 1.083333; this reflects the accurate figure in
decimal hours.
- Enter the formula "=(A2-A1)*1440" (without quotation marks) in cell
A4. The result, formatted as "General", will be 65 (minutes).
- Enter the formula "=(A2-A1)*86400" (without quotation marks) in
cell A5. The result, formatted as "General", 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.
|