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:   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)

jensjakobsen1966's avatar
jensjakobsen1966
Brass Contributor
Jan 27, 2022
Such a beautiful solution - THANK YOU!

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

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.

 

 

  • RobElliott's avatar
    RobElliott
    Silver Contributor
    Jan 29, 2022

    jensjakobsen1966 that's strange and I can't reproduce it; as you can see from the image below, there is no incorrect rounding of the ages on my list:

     

     

    Rob
    Los Gallardos
    Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

    • Gizmaster's avatar
      Gizmaster
      Copper Contributor
      Nov 24, 2022

      That might because of leap years, try to add .25 to 365 at the end the code
      i.e: /365.25*12

      jensjakobsen1966 

    • jensjakobsen1966's avatar
      jensjakobsen1966
      Brass Contributor
      Jan 29, 2022

      RobElliott - thank you for revisiting this subject.

       

      I totally agree with you, and I struggle to explain the error. Can it be something to do with the regional setting for the SharePoint? I've uploaded my settings, if this can shed any light.

       

      Don't do anything, please, I'm happy to use it, even though my results differ a bit.

Resources