Ever wanted to calculate someones age or how long they have been with a company?
1. Create a new List.
2. Create a new column, set the type to Date and Time, with the Date and Time Format set to Date Only.
3. Create a column and name it Today, data type of Single line of text.
4. Create another column and name it Anniversary, type is Calculated (calculation based on other columns), set the data type to Number, and set the decimal place to 0.
The formula is:
=YEAR(Today)-YEAR(HireDate)-IF(OR(MONTH(Today)<MONTH(HireDate),AND(MONTH(Today)=MONTH(HireDate),DAY(Today)<DAY(HireDate))),1,0)
5. Now you want to delete your Today column that you just created (trust me, just try it).
6. Navigate back to your list and try entering a few values. In my example, Today is equal to 8/18/2011.
I have been looking for a similar solution to determine the age of an item in a list. The item is give a “Date Registered” value when the item is added. I followed a similar sequence and used the equation =DATEDIF([Date Registered],Today,”D”) in the calculated column called “Age”. This successfully calculates the age in number of days BUT….is not automatically incremented in subsequent days. I am pretty sure your solution will suffer the same problem only you won’t notice it the lack of update until more than a year has passed. Deleting the Today column stops MOSS reporting a ‘volatile function’ error but also seems to stop it updating automatically.
Thanks. It worked
AMAZING! Works perfectly! Thank you!
This did not work for me. Syntax Error when saving the formula.
What version of SP are you running?
Thank you. This worked for me. Now does it automatically update the “Anniversary” without a workflow?
This formula worked in SharePoint 2016! I also need month in this format – 12 years 6 months. Please let me know how this can be added to the existing formula.
Does this work on sharepoint online ??