We have a board to track employee anniversary dates. I have the hire date and then would like to populate dates into each column at 5 year increments. (5 year anniversary date, 10 year anniversary date, 15 year anniversary date etc). Originally done using General Caster and added the number of days for each anniversary.
TEXT(DATEVALUE({employee’s Hire Date})+ 3650, “YYYY-MM-DD”)
However, this does not account for leap years. I need to add either 60 months or 5 years to the hire date.
I prefer to use General Caster for this, but if not able to, a formula column can work.
Thanks in advance for any assistance.
Hi Angie,
Using the Formula column:
FORMAT_DATE(DATE(YEAR({employee’s Hire Date})+5,MONTH({employee’s Hire Date}),DAY({employee’s Hire Date})),"YYYY-MM-DD")
Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.
Thank you so much!!
Also, if anyone else ever needs this to function using General Caster, the Formula would read:
TEXT(DATE(YEAR({employee’s Hire Date})+5,MONTH({employee’s Hire Date}),DAY({employee’s Hire Date})),“YYYY-MM-DD”)
Could I substitute the +5 with a column that contained a value? for example:
FORMAT_DATE(DATE(YEAR({item’s Completion Date})+({item’s Duration}),MONTH({item’s Completion Date}),DAY({item’s Completion Date})),“YYYY-MM-DD”)
Where Duration column contains a value in years I would like to use to increment the completion date.