Entering dates and times

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 August 15, 2007, is 39309. Noon (halfway through the day) is represented internally as 39309.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:

Tip 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 (date serial number 0). 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. Display the Excel Options dialog box, click the Advanced tab, and then enable the Use 1904 Date System check box. Be aware that switching to the 1904 date system can cause problems with dates already entered in your file or dates in workbooks that are linked to your file.

Tip In some cases, you may need to use time values to represent duration, rather than a point in time. For example, you may need to sum the number of hours worked in a week. When you add time values, 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 example, displays more than 24 hours:

0 0

Post a comment