Microsoft Office Tutorials and References

In Depth Information

20130821

To convert this string to an actual date, you can use a formula such as this one, which assumes the coded date is

in cell A1:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

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.

See 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 you don't care about

which date is earlier and want to avoid displaying a negative value, use this formula:

=ABS(A1-B1)

You can also use the DAYS worksheet function, introduced in Excel 2013. It offers no

advantage that I can see, but here's an example of how to use it to calculate the number

of days between two dates:

=DAYS(A1,B1)

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.