SharePoint Lists - Calculate age from only filled date column

[ppypayap]

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-calculate-age-from-date-of-birt... 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=floor((Number(@now)-Number([$DoB]))/(1000*60*60*24*12)/365*12)"
}

Problem is that age column returns value in every role even some rows are blank. How can in fix this issue or there are other method?

[#2]

@ppypayap you can add a style section and a visibility attribute to make the number in the age column hidden if the date of birth column doesn't have an entry, for example:

{
  "$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": "#008082",
    "padding-left": "4px",
    "color": "white",
    "visibility": "=if(Number[$Birthday]==0,'hidden','visible')"
  }
}

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

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

Thank you so much - very helpful. I have been on hours trying to work that out.... :)