Explanation
Working from the inside out, we use the DATEDIF function to calculate how many complete years are between the original anniversary date and the “as of” date, where the as of date is any date after the anniversary date:
DATEDIF(B5,C5,"y")
Note: in this case, we are arbitrarily fixing the “as of” date as June 1, 2017 in all examples.
Because we are interested in the next anniversary date, we add 1 to the DATEDIF result, then multiply by 12 to convert to years to months.
Next, the month value goes into the EDATE function, with the original date from column B. The EDATE function rolls the original date forward by the number of months given in the previous step which creates the next upcoming anniversary date.
As of today
To calculate the next anniversary as of today, use the TODAY() function for the “as of” date:
=EDATE(date,(DATEDIF(date,TODAY(),"y")+1)*12)
Explanation
This formula depends on the CEILING function , which rounds numbers up to a given multiple. It works because of how dates work in Excel’s default 1900 date system, where the first day is the number 1, which is Sunday, January 1, 1900.
In this scheme, the first Friday is day number 6, the second Friday is day number 13, and day 14 is the second Saturday. What this means is that all second Saturdays in the future are evenly divisible by 14.
The formula uses this fact to figure out 2nd Saturdays, then subtracts 1 to get the Friday previous.
The other Friday
If you need to get the alternate Friday in an every-other-Friday scheme, you can use this version of the formula:
=CEILING(A1+8,14)-8
The idea is the same, but the formula needs to roll forward 8 days to get to an even multiple of 14. Once CEILING returns a date, 8 days are subtracted to move back to the Friday previous.
Note: I ran into this formula as an answer on stack overflow by the awesome Barry Houdini.
