6 month anniversary purchase dates

Smurphster16

New Member
Joined
Feb 28, 2019
Messages
25
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
wouldn't that be?

=EDATE('Calc V2'!E5,6)
 
Upvote 0
Maybe somewhat clunky
=EDATE('Calc V2'!E5,INT((DATEDIF('Calc V2'!E5,TODAY(),"m")/6)+1)*6)
 
Upvote 0
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
 
Upvote 0
Or try:

=EDATE('Calc V2'!E5,CEILING(DATEDIF('Calc V2'!E5,TODAY(),"m")+1,6))
 
Upvote 0
You're welcome & thanks for the feedback.
I'd go with Phuoc's solution.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top