Excel considers dates and times to be values, too. This sounds weird when you first hear it, but treating dates and times as values means you can perform date and time arithmetic. You can easily calculate, for example, by which day you're supposed to pay an invoice due 45 days from today. And you can easily calculate how many hours you work if you start at 6:30 a.m., take a 45-minute lunch, and then continue working until 4:00 p.m.
To enter a date or time value, you just enter a value that looks like a date. Any of the following entries for February 8, 1999, looks like a date to Excel:
February 8, 1999
Feb 8, 1999
8-Feb-99
2-8-99
02-08-99
2/8/99
02/08/99
NOTE: Interestingly, as long as you haven't applied any
specific cell formatting, if you enter a date value using either the first or
second entry shown in the preceding list, Excel converts your entry to the
third entry listed and also displays it this way. Similarly, if you enter a
date value using the fourth entry shown in the preceding list, Excel converts
your entry to the fifth entry listed and also displays it this way. Although
these conversions may seem strange, what's really happening is this: Excel
recognizes that your entry is a date value, sees it has been entered incorrectly,
and then converts it to an accepted date value syntax.
Time values work in a fashion that is similar to date values. If you enter into a cell something that looks like a time, for example:
1:00
2:30 a.m.
5:00:01 p.m.
16:45
Excel treats your entry as a time value. (Excel considers the entry 1:00 to be 1:00 a.m., by the way.) If you enter something that resembles a time value, but doesn't actually use the correct syntax—"1:00 p" for example—Excel edits your entry so it does use the correct syntax.
Here's another weird little twist on date and time values. Although you can enter date and time values in the way just described—as little snippets of characters that look like a date or time—you can also enter them as regular values and then later format them to look like dates or times. According to Excel, for example, the integer 1, represents January 1, 1900. The integer 2 represents January 2, 1900. And the integer 3 represents January 3, 1900. And so on.
TIP: When you enter
date or time values that look like date or time values, you're actually
including formatting with the value. In other words, you're simultaneously
entering a value into a cell and also telling Excel how to display, or format,
the cell.
Excel uses decimal values to represent time values. The decimal value 0.00, for example, represents 12:00:00 a.m. The decimal value 0.25 represents 6:00:00 a.m. The decimal value 0.5 represents 12:00:00 p.m., and so on. This business about time values being decimal values should make sense if you think about it for a minute. If Excel uses the whole number 1 to represent an entire day, then values less than one—in other words, decimal values—must be used to represent units of time less than a day: seconds, minutes, hours, and so forth.
Excel also lets you combine integers and decimal values to create date-and-time value combinations. For example, the value 1.0 represents January 1, 1900, 12:00:00 a.m. The value 2.25 represents January 2, 1900, 6:00:00 a.m. The value 3.5 represents January 3, 1900, 12:00:00 p.m.
Working with values that you enter as values is usually too confusing. You won't know that the value 36,502.2 represents 4:48 a.m., December 8, 1999, until you format it. And, in fact, if you did enter the value 36,502.2 and then formatted it as a date, Excel would replace your entry (36,502.2) with the date value 12/8/1999 4:48 a.m.
NOTE: For more information about formatting values, refer to
Chapter 3.
But you should still remember something about all this: Each day's date value is one more than the previous day's date value. If you remember this, you'll be able to easily construct formulas that manipulate date values by adding values to date values. For example, if you want to know the precise date that falls 45 days after the date January 25, 1997, you can add the value 45 to the date value 1/25/1997.