Problem with EDATE function

  • Thread starter Thread starter C Money
  • Start date Start date
C

C Money

I'm creating a commissions statement, and the statement
issued is always for the previous month. For instance
we're in June currently, but the statements are being
issued for sales in May. I know you can refer back a month
using the EDATE functiong but I'm not sure how to do it.
Do I need to have two cells to do this, one for the
current month and then another one to use the edate
function to list the previous month or can I do all of
this in one cell?
 
Did you look in HELP index for EDATE?
Returns the serial number that represents the date that is the indicated
number of months before or after a specified date (the start_date). Use
EDATE to calculate maturity dates or due dates that fall on the same day of
the month as the date of issue.
If this function is not available, run the Setup program to install the
Analysis ToolPak. After you install the Analysis ToolPak, you must enable it
by using the Add-Ins command on the Tools menu.

How?

Syntax

EDATE(start_date,months)

Start_date is a date that represents the start date.

Months is the number of months before or after start_date. A positive
value for months yields a future date; a negative value yields a past date.

Remarks

· If start_date is not a valid date, EDATE returns the #NUM! error value.
· If months is not an integer, it is truncated.

Examples

EDATE(DATEVALUE("01/15/91"),1) equals 33284 or 02/15/91

EDATE(DATEVALUE("03/31/91"),-1) equals 33297 or 02/28/91
 
You can accomplish it in a single cell, but it would take a little more
effort to maintain it. As per the Excel help on this function, the EDATE
function has the following syntax:

EDATE(start_date,months)

where:

start_date
is a date that represents the start date. Dates should be entered by
using the DATE function, or as results of other formulas or functions. For
example, use
DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if
dates are entered as text.

So if you wanted to use a single cell, your formula would have to be
something like:

=EDATE(DATE(2008,5,23),-1)

to return the date April 23, 2008.

HTH
--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
 
Back
Top