6 month anniversary purchase dates

[Smurphster16]

Hi guys,

I was looking to create a formula which based on a fixed purchase date gives me the next future 6 month anniversary date of that purchase date.

So if the purchase date was 31/03/2018 I would (today) want my formula to return the date 31/09/2019

The original formula I had was : =EDATE('Calc V2'!E5,(DATEDIF('Calc V2'!E5,TODAY(),"m")+4))

but my issue is the 4 at the end of the formula will need to change dependent on what month it is so that the formula adds 24 months, 32 months, 38 months e.t.c to the purchase date.

Can anyone help me with this?

Many thanks,

Phil

 

[AlanY]

wouldn't that be?

=EDATE('Calc V2'!E5,6)

 

[Fluff]

Maybe somewhat clunky
=EDATE('Calc V2'!E5,INT((DATEDIF('Calc V2'!E5,TODAY(),"m")/6)+1)*6)

 

[Smurphster16]

My issue is that I always want the date returned by the formula to be in the future - so in this case the 6 and 12 month anniversary's have passed and so I would want the 18th month anniversary (so effectively I want the rolling 6 month anniversary in the future)

sorry for the confusion,

Thanks

 

[Fluff]

Did you see post#3?

 

[Phuoc]

Or try:

=EDATE('Calc V2'!E5,CEILING(DATEDIF('Calc V2'!E5,TODAY(),"m")+1,6))

 

[Smurphster16]

Did you see post#3?

Sorry Fluff I didn't see post 3, both of the int and ceiling solutions work, thanks very much!

 

[Fluff]

You're welcome & thanks for the feedback.
I'd go with Phuoc's solution.