Microsoft Office Tutorials and References

In Depth Information

**Converting a nondate string to a date**

To generate a series of dates separated by one month, you need to use a more complicated

formula because months don’t all have the same number of days. This formula creates a

series of dates, separated by one month:

16

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

To generate a series of dates separated by one year, use this formula:

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

To generate a series of weekdays only (no Saturdays or Sundays), use the following formula.

This formula assumes that the date in cell A1 is not a weekend day:

=IF(WEEKDAY(A1)=6,A1+3,A1+1)

Converting a nondate string to a date

You may import data that contains dates coded as text strings. For example, the following

text represents August 21, 2013 (a four-digit year followed by a two-digit month, followed

by a two-digit day):

20130821

To convert this string to an actual date, you can use a formula, such as the following. (This

formula assumes that the coded data 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 it

uses these extracted digits as arguments for the
DATE
function.

Calculating the number of days between two dates

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

example, say you have a ﬁ nancial 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-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)