Forum Discussion
Date calculation on SharePoint lists between today and a birthdate
- Jan 27, 2022
jensjakobsen1966 there are a couple of ways to do this. One is with a flow in Power Automate that runs once a day and does the calculation. Another is to have a number column and format it with JSON as follows:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json", "elmType": "div", "txtContent": "=floor((Number(@now)-Number([$Birthday]))/(1000*60*60*24*12)/365*12)" }
The result is:
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
I solved the List displaying all the ages as the same by replacing the formula and using a Calculated Age column instead of Formatting. My formula successfully used DATEDIF to calculate the DIFference between two DATEs. Calculated columns in Lists are read-only so to help the person entering data in the Lists, I used the IF(ISBLANK) command to display a message if the Birthdate was left blank.
Here's how I created the Calculated Column in your SharePoint list:
- In SharePoint list column headings, click +Add column.
- Name your column (e.g., "Age").
- Select "Calculated (calculation based on other columns)" as the column type.
- In the formula field, enter the following formula to calculate the age:
=IF(ISBLANK(Birthdate),"Auto-calculated from Birthdate",DATEDIF(Birthdate,TODAY(),"Y"))
Notes:
IF(ISBLANK(Birthdate) can be changed to "" if you want Age left blank.
DATEDIF Y for years can also be changed to M for months or D for days, as appropriate for what you want to display in Age. - Set the data type returned from this formula to "Number."
- Click "OK" to save the column.