Hi all, I have dates in column D and I am using this formula “=IF(DATE(YEAR(TODAY()),MONTH(D3),DAY(D3))>DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),DATE(YEAR(TODAY()),MONTH(D3),DAY(D3)),DATE(YEAR(TODAY())+1,MONTH(D3),DAY(D3)))” in next column to determine the anniversary dates, the formula is working fine. Can anyone suggest an alternative formula which is shorter and more sophisticated? Thanks in advance.
An alternative formula
- Thread starter razaas
- Start date
Thanks again Hui, I needed to get the anniversary dates if the month and day already passed then in that case the result should be next years' date. If the month and day yet to pass then current year, for which your second formula fails as you can see below the dates on right side. Also the first formula is easy to understand and that is highly appreciated. Thanks
15-Jan-15 15-Jan-15
21-Mar-15 21-Mar-15
10-Jul-15 10-Jul-15
16-Dec-14 16-Dec-12
19-Dec-14 19-Dec-12
15-Jan-15 15-Jan-15
21-Mar-15 21-Mar-15
10-Jul-15 10-Jul-15
16-Dec-14 16-Dec-12
19-Dec-14 19-Dec-12
I'm confused
As both my formulas give the same result?
Your date 1st formula 2nd Formula
15/01/2015 15/01/2015 15/01/2015
21/03/2015 21/03/2015 21/03/2015
10/07/2015 10/07/2015 10/07/2015
16/12/2014 16/12/2014 16/12/2014
19/12/2014 19/12/2014 19/12/2014
In the first 3 rows the Day/Month has already passed and so it gives next years date
In the last 2 rows it is this years date
As both my formulas give the same result?
Your date 1st formula 2nd Formula
15/01/2015 15/01/2015 15/01/2015
21/03/2015 21/03/2015 21/03/2015
10/07/2015 10/07/2015 10/07/2015
16/12/2014 16/12/2014 16/12/2014
19/12/2014 19/12/2014 19/12/2014
In the first 3 rows the Day/Month has already passed and so it gives next years date
In the last 2 rows it is this years date
Thanks Hui,
Your 1st formula is really good and I am very happy with it as I compare it with my crude and layman kind of formula.
You are so kind to provide another formula which seems not working as I desired, the reason might be that you are not aware of the actual dates I have which are now shown below.
Actual Dates 1st Formula 2nd Formula
15-Jan-12 15-Jan-15 15-Jan-15
21-Mar-12 21-Mar-15 21-Mar-15
10-Jul-12 10-Jul-15 10-Jul-15
16-Dec-12 16-Dec-14 16-Dec-12
19-Dec-12 19-Dec-14 19-Dec-12
I know with each new solution there is a lot to learn and especially the kind of solutions you provides, they are always awesome.
Thanks for your support and have a great day.
Your 1st formula is really good and I am very happy with it as I compare it with my crude and layman kind of formula.
You are so kind to provide another formula which seems not working as I desired, the reason might be that you are not aware of the actual dates I have which are now shown below.
Actual Dates 1st Formula 2nd Formula
15-Jan-12 15-Jan-15 15-Jan-15
21-Mar-12 21-Mar-15 21-Mar-15
10-Jul-12 10-Jul-15 10-Jul-15
16-Dec-12 16-Dec-14 16-Dec-12
19-Dec-12 19-Dec-14 19-Dec-12
I know with each new solution there is a lot to learn and especially the kind of solutions you provides, they are always awesome.
Thanks for your support and have a great day.