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 store as consecutive serial numbers, you can use simple subtrac-
tion 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:
Excel will automatically format this formula cell as a date, rather than a numeric
value. Therefore, you will need to change the number format so the result is dis-
played as a non-date. 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 lookslike a date.
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 promo-
tion 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, 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 you have appropriately named cells) appears
Calculating the Number of Work Days
between Two Dates
When calculating the difference between two dates, you may want to exclude
weekends and holidays. For example, you may need to know how many business
days fall in the month of November. This calculation should exclude Saturdays,
Sundays, and holidays. The NETWORKDAYS function can help out. (You can access
this function only when you install the Analysis ToolPak.)
Part II: Using Functions in Your Formulas