Date formula

[nibbana]

I want to add 12 months to a start date (inclusive of the start date) and
return the end date.
Start date will only be the first or last day of the month.
Calculated end date must only return the first or last day of the month.

Eg:

Start 06/30/2009
Add 12 months
Result should = 05/31/2010

Eg:

Start 06/01/2009
Add 12 months
Result should = 05/01/2009

Thanks!

 

[Rick Rothstein]

Why does adding 12 months (1 year) to the beginning or end of June take you
to the month of May? If that is correct, then this formula should do what
you want...

=A1-DAY(A1)-(DAY(A1)=1)*((DAY(A1-DAY(A1))-1))

If you will copy this formula down for cells in Column A that are blank,
then use this variation to suppress the zero-date that would be returned for
blank cells...

=IF(A3="","",A3-DAY(A3)-(DAY(A3)=1)*((DAY(A3-DAY(A3))-1)))

 

[nibbana]

That's right Rick. I want 12 (or other variable number) billing periods that
include the start date as one of them. You're formula result goes back 1 year
instead of forward. Can you post through an amendment? Here's my actual test:

A1: 30-Jun-06
A2: 12 (or any other variable periods)
A3: 31-May-07 (your formula goes here)

Thanks!

 

[Rick Rothstein]

Oh, you want a variable time period (you didn't say that initially)... then
the approach I tried to use in my previous response cannot be patched to
handle that. Try this instead. First, make sure you have the Analysis
ToolPak Add-In selected (Tools/Add-Ins from the menu bar) and then put this
formula in A3...

=IF(A1="","",EOMONTH(A1,A2-1-(DAY(A1)=1))+(DAY(A1)=1))

 

[nibbana]

Mate, you're a legend. Well done that works a treat.