Working with Dates and Times


Excel uses a serial number system to store dates. The earliest date that Excel can understand is January 1, 1900. This date has a serial number of 1. January 2, 1900, has a serial number of 2, and so on.

Most of the time, you don't have to be concerned with Excel's serial number date system. You simply enter a date in a familiar date format, and Excel takes care of the details behind the scenes. For example, if you need to enter August 15, 2007, you can simply enter the date by typing August 15, 2007 (or use any of a number of different date formats). Excel interprets your entry and stores the value 39309, which is the serial number for that date.

Note  

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

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:

 =A2-A1 
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:

 [hh]:mm 

Using pre-1900 dates

The world, of course, didn't begin on January 1, 1900. People who work with historical information when using Excel often need to work with dates before January 1, 1900. Unfortunately, the only way to work with pre-1900 dates is to enter the date into a cell as text. For example, you can enter the following into a cell, and Excel won't complain:

 July 4, 1776 

You can't, however, perform any manipulation on dates that are actually text. For example, you can't change its formatting, you can't determine which day of the week this date occurred on, and you can't calculate the date that occurs seven days later. (See Figure 3-9.)

image from book
Figure 3-9: The Extended Date Functions add-in lets you work with pre-1900 dates.



Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net