=Date formula need to change depending on month

[rsm8275]

I’m attempting to create a Records Retention Schedule spreadsheet. I want to use System Date, or any entered date and then calculate specific dates that a given record may be destroyed in the future or determine which old records are due/overdue for destruction.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The different retention periods include DATE plus or minus: 1 year, 2 years, and Calendar Year End ± x years. I have figured out how to do those with help for Mr. Excel videos and this great forum.
<o></o>
Where I need help is for Fiscal Year End + x years. Fiscal Year End for my organization is 08-31-yy. My attempt for FE+3 is =DATE(YEAR(A1)+4,1,-121).
That works when the DATE is January – August but on September 1<SUP>st</SUP> it needs to increase an additional year.
<o></o>
Any help with this formula would be greatly appreciated.
<o></o>
PS - to make it possibly harder we only have Excel 2003 without the Analysis ToolPak.
<o></o>
Thanks,
<o></o>
Ron

 

[RolfJ]

This modified formula might do the trick:

=IF(MONTH(A1)<9,DATE(YEAR(A1)+4,1,-121),DATE(YEAR(A1)+5,1,-121))

Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com

 

[rsm8275]

Wow, that is outstanding. Exactly what I needed Rolf.

Thank you very much.

Ron