Forum Discussion
jensjakobsen1966
Jan 27, 2022Brass Contributor
Date calculation on SharePoint lists between today and a birthdate
Hi I've tried to calculate the age of a person based on 2 columns (NO success at all) on a SharePoint list.
Therefore I need help, from the ground:
- Birthday column is a DATE column
- Today's date - how do I create that?
- Age in years (no decimals) between column 1 and 2 - what column type and calculation should I use?
Thanks.
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)
- David_Ikrash_001Copper Contributor
To calculate the difference between today’s date and a birthdate in a SharePoint list, you can use calculated columns with the formula =DATEDIF([Birthdate], TODAY(), "Y"). This formula returns the age in years. You can also modify it to calculate months or days by changing the "Y" to "M" or "D", respectively. Make sure the Birthdate column is of the Date type. This method will dynamically update the age daily as today's date changes.
- S N AmiCopper Contributor
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.
- RobElliottSilver Contributor
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)- Bo_Li_ChengCopper Contributor
RobElliott
I used the exact same thing but every result returned 53. Are there some other settings I may have missed?- Edolop67Copper Contributor
- jinibobCopper ContributorHi,
How do you do this using PowerAutomate? - jensjakobsen1966Brass ContributorSuch a beautiful solution - THANK YOU!
🙂- jensjakobsen1966Brass Contributor
Hi again RobElliott - I believe there's an error. And I'm sorry to bother you again.
Whenever a person has his/hers birthday within January to July, the formula will round up the age, even though the person has not yet had their birthday.