EDATE formula ???

[pangx623]

Please help..

I'm looking for a formula that subtracts the number of months from a date using the edate function.

example:

Date = 04/30/2007 - Cell A1
Credit = 4 - This cell is an integer - Cell B1

The difference should return 12/31/2006, but i'm getting 12/30/2006? I believe it's caused by the overlapping year.

The formula i'm using is =EDATE(A1,-B1)

 

[unmarkedhelicopter]

If you had the 12th of January and you subtracted 1 month you'd expect to get the 12th December, so when you have the 30th how is the formula supposed to know you want the last day of the month instead ?
If you DO want the last day of the month (in each answer) I think you'll need a UDF (User Defined Function) to do it. Someone would have to write this in VBA but they'd need a lot more information, before they start.
EDATE is not part of standard Excel functionality (at least not in Excel 2003) so if it's part of an add in you need to say which one or if it's 2007 again we need to know.

 

[clive2002]

Analysis ToolPak

Your using the analysis toolpak addin formulas.

The formula your using is doing exactly what you said you wanted return the same day four months ago.

e.g. 30-Apr-07 = 30-DEC-07
12-Apr-07 = 12-DEC-07

Nothing to do with year overlap.

You need to clarify.

You want the last day of December because the 30th April happens to be the last day of April or do you always want the last day of the month??

If you always want the last day of the month you can use EOMONTH

ColumnA Formula Returns

EDATE
30-Apr-07 =EDATE(A4,-4) 30-Dec-06
12-Apr-07 =EDATE(A5,-4) 12-Dec-06

EOMONTH
30-Apr-07 =EOMONTH(A8,-4) 31-Dec-06
12-Apr-07 =EOMONTH(A9,-4) 31-Dec-06

 

[pangx623]

Your using the analysis toolpak addin formulas.

The formula your using is doing exactly what you said you wanted return the same day four months ago.

e.g. 30-Apr-07 = 30-DEC-07
12-Apr-07 = 12-DEC-07

Nothing to do with year overlap.

You need to clarify.

You want the last day of December because the 30th April happens to be the last day of April or do you always want the last day of the month??

If you always want the last day of the month you can use EOMONTH

ColumnA Formula Returns

EDATE
30-Apr-07 =EDATE(A4,-4) 30-Dec-06
12-Apr-07 =EDATE(A5,-4) 12-Dec-06

EOMONTH
30-Apr-07 =EOMONTH(A8,-4) 31-Dec-06
12-Apr-07 =EOMONTH(A9,-4) 31-Dec-06

This works, thank you!!!