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 fi 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)
Search JabSto ::




Custom Search