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
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

Search JabSto ::

Custom Search