Microsoft Office Tutorials and References

In Depth Information

**Date-Related Functions**

This formula uses text functions (LEFT, MID, and RIGHT) to extract the digits and then uses these

extracted digits as arguments for the DATE function.

Refer to Chapter 5 for more information about using formulas to manipulate text.

Calculating the number of days between two dates

A common type of date calculation determines the number of days between two dates. For

example, you may have a financial worksheet that calculates interest earned on a deposit

account. The interest earned depends on how many days that the account is open. If your sheet

contains the open date and the close date for the account, you can calculate the number of days

the account was open.

Because dates store as consecutive serial numbers, you can use simple subtraction to calculate

the number of days between two dates. For example, if cells A1 and B1 both contain a date, the

following formula returns the number of days between these dates:

=A1-B1

If cell B1 contains a more recent date than the date in cell A1, the result will be negative.

If this formula does not display the correct value, make sure that A1 and B1 both contain

actual dates — not text that
looks
like dates.

Sometimes, calculating the difference between two days is more difficult. To demonstrate,

consider the common “fence post” analogy. If somebody asks you how many units make up a fence,

you can respond with either of two answers: the number of fence posts, or the number of gaps

between the fence posts. The number of fence posts is always one more than the number of gaps

between the posts.

To bring this analogy into the realm of dates, suppose you start a sales promotion on February 1

and end the promotion on February 9. How many days was the promotion in effect? Subtracting

February 1 from February 9 produces an answer of eight days. However, the promotion actually

lasted nine days. In this case, the correct answer involves counting the fence posts, as it were,

and not the gaps. The formula to calculate the length of the promotion (assuming you have

appropriately named cells) appears like this:

=EndDay-StartDay+1