I wanted to add a column on a SharePoint modern experience list which showed the number of days since it was created. I also wanted to colour the dates so that if it was over a certain number of days it would display as yellow or red.

I found very little examples online, so I decided to post my answer.

The first thing I did was create a calculated field from the created field. You can use any date here.

I then added this field to the view, and used the following formula in the JSON Formatter

{
“$schema”: “http://columnformatting.sharepointpnp.com/columnFormattingSchema.json”,
“elmType”: “div”,
“attributes”: {
“class”: “=if(ceiling(Number(Date(@now))/1000/60/60/24)-ceiling(Number(Date(@currentField))/1000/60/60/24) >= 7,’sp-field-severity–severeWarning’, if(ceiling(Number(Date(@now))/1000/60/60/24)-ceiling(Number(Date(@currentField))/1000/60/60/24) >= 3,’sp-field-severity–warning’, ‘sp-field-severity–good’)”
},
“txtContent”: “=ceiling(Number(Date(@now))/1000/60/60/24)-ceiling(Number(Date(@currentField))/1000/60/60/24)”
}

To break this down, I was able to convert the date to a number, which converted it to the number of milliseconds since EPOC using =Date(@currentField) and =Date(@now). I substracted the fields date from today. I then added a conditional class based on this number and the fixed numbers 3 (For warning) and 7 (for severe)

After thought: While writing this blog, I realised I could have converted the dates after doing the date subtraction operation.