Forum Discussion

ppypayap's avatar
ppypayap
Copper Contributor
Dec 05, 2022

SharePoint Lists - Calculate age from only filled date column

Hi,   I would like to get automatic age column calculated by today date and date of birth column. I have tried using this method https://techcommunity.microsoft.com/t5/sharepoint/lists-formulas-for...
Lists
microsoft lists
SharePoint Online
jonna1973's avatar
jonna1973
Copper Contributor
Jan 23, 2023

Hi RobElliott is it possible to add to this JSON that if number X is displayed, colour of number is XX, if number Y is displayed, colour of number is YY, if number Z is displayed, colour of number is ZZ

 

Any help would be appreciated

RobElliott's avatar
RobElliott
Silver Contributor
Jan 23, 2023

jonna1973 yes you can set the colour depending on the value, for example:

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if(@currentField <= 8, 'red', (if(@currentField >= 9 && @currentField <= 16,'orange', 'green'))",
    "padding-left": "10px"
  }
}



 

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

  • jonna1973's avatar
    jonna1973
    Copper Contributor
    Jan 23, 2023
    Thank you for the quick reply and info. Is it possible to combine both? the date of birth JSON and the one just provided. Very much appreciated 🙂
    • RobElliott's avatar
      RobElliott
      Silver Contributor
      Jan 23, 2023

      jonna1973 yes, you'd combine them 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)",
  "style": {
    "background-color": "#c9f2c9",
    "color": "=if(floor((Number(@now)-Number([$Birthday]))/(1000*60*60*24*12)/365*12) == 20,'#196958', if(floor((Number(@now)-Number([$Birthday]))/(1000*60*60*24*12)/365*12) == 30, '#ffa900', if(floor((Number(@now)-Number([$Birthday]))/(1000*60*60*24*12)/365*12) == 40, '#a00000', '#b5c2bc')",
    "padding-left": "4px",
    "visibility": "=if(Number[$Birthday]==0,'hidden','visible')"
  }
}

       

       

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

      • jonna1973's avatar
        jonna1973
        Copper Contributor
        Jan 23, 2023
        Thank you so much - very helpful. I have been on hours trying to work that out.... 🙂

Resources