6 month anniversary purchase dates

Smurphster16

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

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
wouldn't that be?

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,727
Office Version
  1. 365
Platform
  1. Windows
Maybe somewhat clunky
=EDATE('Calc V2'!E5,INT((DATEDIF('Calc V2'!E5,TODAY(),"m")/6)+1)*6)
 

Smurphster16

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,727
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Did you see post#3?
 

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
280
Or try:

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,727
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
I'd go with Phuoc's solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

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
Top