When working with times, you simply enter the time into a cell in a recognized format. Excel's system for representing dates as individual values is extended to include decimals that represent portions or fractions of days. In other words, Excel perceives all time with the same system whether that time is a particular day, a certain hour, or a specific second. For example, the date serial number for June 1, 2004, is 38139. Noon (halfway through the day) is represented internally as 38139.5. Again, you normally don't have to be concerned with these fractional serial numbers.
Because dates and times are stored as serial numbers, it stands to reason that you can add and subtract dates and times. For example, you can enter a formula to calculate the number of days between two dates. If cells A1 and A2 both contain dates, the following formula returns the number of intervening days:
When performing calculations with time, things get a bit trickier. When you enter a time without an associated date, the date is assumed to be January 0, 1900. This is not a problem — unless your calculation produces a negative time value. When this happens, Excel displays an error (displayed as #########).The solution? Switch to the 1904 date system. Choose Tools ^ Options, click the Calculation tab, and then enable the 1904 Date System check box. Be aware that switching to the 1904 date system can cause problems with either dates already entered in your file or dates in workbooks that are linked to your file.
When you add time values, you'll find that you can't display more than 24 hours. For each 24-hour period, Excel simply adds another day to the total. The solution is to change the number formatting to use square brackets around the hour part of the format.The following number format, for exam-ple,displays more than 24 hours: [hh]:mm
Was this article helpful?