How to Calculate Age from Date of Birth in Microsoft Lists
Question details
The user needs to dynamically calculate and display a person's age in years based on a Date of Birth column within a Microsoft List.
- Product
- Microsoft Lists
- Device & OS
- not provided
- Scenario
- Managing user, student, or employee records where an up-to-date age needs to be automatically computed from their birth date without manual updates.
- Observed behavior
- Requires configuring a Calculated column and utilizing advanced column formatting to correctly render the age difference between the birth date and the current date.
Ensure you have 'Edit' or 'Full Control' permissions for the Microsoft List so you can add new columns and modify column formatting.
Use a Calculated Column with Advanced Formatting
Create the necessary date columns and apply a JSON formula in the advanced formatting settings to dynamically calculate the age based on today's date.
In Microsoft Lists, standard calculated columns using the TODAY() function only update when the list item is modified. To ensure the age updates automatically every day, you must use column formatting with the dynamic '@now' token.
Open your Microsoft List. Click on 'Add column', select 'Date and time', name the column 'Date of Birth', and click 'Save'.
Click 'Add column' again, select 'Calculated' (or 'Number' if you prefer formatting a blank column), and name it 'Age'. If prompted for a formula, you can enter a simple reference like =[Date of Birth] just to initialize the column.
Click on the header of the newly created 'Age' column. In the dropdown menu, select 'Column settings' and then click on 'Format this column'.
At the bottom of the formatting pane on the right, click 'Advanced mode'. Paste the JSON code that subtracts the 'Date of Birth' from the current date ('@now'), divides the result by the number of milliseconds in a year (31556952000), and formats it to display as a whole number. Click 'Save' to apply the calculation.
Manage Tabular Data Easily with WPS Office
If you are managing lists of personnel and need to calculate ages or manipulate data without using complex JSON code, WPS Spreadsheet provides an intuitive, offline-capable, and entirely free alternative for data management.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank Spreadsheet.
- 2. Enter Your Data: Type 'Date of Birth' in column A and input your dates. Type 'Age' in column B.
- 3. Apply the Formula: In the Age column, type the formula =DATEDIF(A2, TODAY(), "Y") and press Enter. Drag the fill handle down to calculate ages for the entire list instantly.
Frequently Asked Questions
Why isn't my Microsoft Lists age calculation updating daily?
If you use the standard TODAY() function in a traditional SharePoint/Microsoft Lists calculated column, the value is stamped at the time the item is created or edited. It does not update automatically every day. Using column formatting with the '@now' token ensures the age renders dynamically on page load.
Can I use the DATEDIF function directly in Microsoft Lists?
While DATEDIF works perfectly in Excel and WPS Spreadsheet, Microsoft Lists Calculated columns have limitations with dynamic daily dates. DATEDIF will only calculate the age based on the day the record was last modified, which is why JSON formatting is the recommended approach for age.
How do I find the internal name of my Date of Birth column for the JSON code?
Go to 'List settings' (via the gear icon), scroll down to your columns, and click on 'Date of Birth'. Look at the URL in your browser's address bar; the internal name is listed at the very end after 'Field='. It often removes spaces, looking like 'DateofBirth'.
