I've been trying to use a formula that will auto-populate the next anniversary date for an employee.

The formula I am using will get the correct year, but not the month and day right. Here's what I am using:

=edate(E3,(datedif(E3,TODAY(),"y")+1)*12)

Here's a link to the spreadsheet if you'd care to take a look.

https://docs.google.com/spreadsheets...it?usp=sharing

Any suggestions are appreciated!

On 2/23/2019 3:06 PM, wrote:
I've been trying to use a formula that will auto-populate the next anniversary date for an employee.
....

I'd have to go delve into the bowels of Excel date functions, but if I
understand your want/need, the logic is

if(datevalue(year(NOW(),mon(emplDate),day(emplDate )) = NOW(),
datevalue(year(NOW(),mon(emplDate),day(emplDate),
datevalue(year(NOW()+1,mon(emplDate),day(emplDate) )

That is, if employee anniversary for the year is coming up, it's this
year, it it has passsed, then it's this year + 1.

This will be actual calendar date, doesn't account for work days, etc.,
if that's of significance.

--

On 2/23/2019 3:06 PM, wrote:
I've been trying to use a formula that will auto-populate the next anniversary date for an employee.

....

Try

=IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))=TODAY() ,
DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),
DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1)))

Logic as in previous comment, it takes me some time to wrap my head
around Excel function syntax and delve out which is the one that has the
right form as being text/date/number/etc., ...

For the employee anniversary date in A1.

Could recast the IF() into a computed 0|1 addend to the year if desired;
this is the "deadahead" implementation of the thought process of how to
determine.


=DATE(YEAR(TODAY())+N(DATE(YEAR(TODAY()),MONTH(A1) ,DAY(A1))=TODAY()),MONTH(A1),DAY(A1)))

is off-the-cuff recast...untested. Particularly check for matching
parentheses.

--

On 2/24/2019 10:01 AM, dpb wrote:
....

=DATE(YEAR(TODAY())+N(DATE(YEAR(TODAY()),MONTH(A1) ,DAY(A1))=TODAY()),MONTH(A1),DAY(A1)))


is off-the-cuff recast...untested.Â* Particularly check for matching
parentheses.

And indeed, the logic test is reversed in the above..


=DATE(YEAR(TODAY())+N(DATE(YEAR(TODAY()),MONTH(A1) ,DAY(A1))TODAY()),MONTH(A1),DAY(A1)))


--