Forum Discussion

jensjakobsen1966's avatar
jensjakobsen1966
Brass Contributor
Jan 27, 2022

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:

 

  1. Birthday column is a DATE column
  2. Today's date - how do I create that?
  3. 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)

  • 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 Ami's avatar
    S N Ami
    Copper 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:

    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.
  • RobElliott's avatar
    RobElliott
    Silver 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_Cheng's avatar
      Bo_Li_Cheng
      Copper Contributor

      RobElliott 

      I used the exact same thing but every result returned 53. Are there some other settings I may have missed?

       

       

    • jinibob's avatar
      jinibob
      Copper Contributor
      Hi,

      How do you do this using PowerAutomate?
      • jensjakobsen1966's avatar
        jensjakobsen1966
        Brass 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.

         

         

Resources