Microsoft Office Tutorials and References

In Depth Information

**Using Date Math**

Figure 9.19.

Figure 9.19.
Although cell E1 is formatted as a date, Excel stores the date

Although cell E1 is formatted as a date, Excel stores the date

as the number of days since January 1, 1900.

as the number of days since January 1, 1900.

This convenient system enables you to do some pretty simple math. For ex-

ample,
Figure 9.20
shows a range of invoice dates in column B. The terms for

the invoice are in column D. You can calculate the due date by adding cells

B2 and D2. Here is what actually happens in Excel
’
s calculation engine:

11. The date in cell B2
—
2/1/2015
—
is stored as 42036.

22. Excel adds 10 to that number to get the answer 42046.

33. Excel formats this number as a date, to yield 2/11/2015.

Figure 9.20.

Figure 9.20.
When the answer is formatted correctly, Excel
’
s date math is

When the answer is formatted correctly, Excel
’
s date math is

very cool.

very cool.

However, a frustrating problem can occur if the cell containing the formula

has the wrong numeric format. For example, in
Figure 9.21
, the WORKDAY

function in column D did not automatically convert the result to a date. It is