Part II: Working with Formulas and Functions
262
This formula uses text functions (LEFT, MID, and RIGHT) to extract the digits, and then it uses
these extracted digits as arguments for the DATE function.Cross-Reference
See Chapter 11 for more information about using formulas to manipulate text. n
Calculating the number of days between two dates .................................................
A common type of date calculation determines the number of days between two dates. For exam-
ple, say you have a financial worksheet that calculates interest earned on a deposit account. The
interest earned depends on the number of days 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 are stored 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-B1If cell B1 contains a more recent date than the date in cell A1, the result will be negative.Note
If this formula does not display the correct value, make sure that A1 and B1 both contain actual dates — not
text that looks like a date. n
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 that 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. Actually, though, the promotion
lasted nine days. In this case, the correct answer involves counting the fence posts, not the gaps.
The formula to calculate the length of the promotion (assuming that you have appropriately named
cells) appears like this:=EndDay-StartDay+1