Forum Discussion

jensjakobsen1966's avatar
jensjakobsen1966
Brass Contributor
Jan 27, 2022
Solved

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 ...
Lists
SharePoint Online
  • RobElliott's avatar
    RobElliott
    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)

S N Ami's avatar
S N Ami
MCT
Feb 06, 2025

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:

  1. In SharePoint list column headings, click +Add column.
  2. Name your column (e.g., "Age").
  3. Select "Calculated (calculation based on other columns)" as the column type.
  4. 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.

  5. Set the data type returned from this formula to "Number."
  6. Click "OK" to save the column.

Resources