Microsoft Office Tutorials and References

In Depth Information

**Working with Dates and Times**

In this chapter, I assume the U.S. date system. If your computer uses a different date

system, you’ll need to adjust accordingly. For example, you might need to enter 15

August, 2010.

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, 2010, is 40405. Noon (halfway through the day) is represented

internally as 40405.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:

=A2-A1

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.

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:

[hh]:mm