How to create a single JSON code to do multiple things

Question

I track training for many customer and sometimes we enter the date the training was completed and other times we enter the expiration date. Each column is a little different depending on the client and date.

I have spent hours googling and trying to figure this out and can't. Is this even possible? I need 2 different JSON single codes to insert into my lists (per column) to have certain formatting. Ideally, I would like the entire cell to change color but if just the text changes, that is fine too.

The first code I need is for when we enter the "Date Completed". I need the code to do all of the below:

Turns the text Yellow after a certain amount of days from the date entered in the cell (then I can set this to however many days/years the training is good for) and keep it yellow until it is updated or until turns Red

Turns the text Red after a certain amount of days and keeps it red until it is updated

Puts N/A in the cell if the cell is blank

Turns the date “1/1/1900” red automatically

(example:  If the training is good for 1 year and they completed it today, we would enter “11/12/2023” – I would want it to turn yellow after 320 days and then red at 365 days and stay red for all days after 365 until updated)

 

I also need a single code to enter for the expiration date columns that does the below:

Turns text yellow when 45 days away from the date entered

Turns red when it hits the date entered or any day after that date until it is updated

Puts “N/A in the cell if the cell is blank

Turns the date” 1/1/1900” red automatically

(example:  For these, not as common but used when tracking date of driver’s license expiration or something similar when not all dates are good for the same amount of time.  If there license expires on 10/20/2026 then we would enter “10/20/2026” and I would need it to turn Yellow 45 days before 10/20/26 and then turn red at 10/20/26 and stay red until it is updated.)

Answer 1

Hi @Julia Wade,

You could use the JSON formatting based on conditions and updating the styles of the component. You can modify this styles of a column : Use column formatting to customize SharePoint - styles. Please refer to the sample

    {
       "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
       "elmType": "div",
       "txtContent": "@currentField",
       "style": {
          "background-color": "=if(@currentField == 'A', '#2BBBAD', if(@currentField == 'B', '#4285F4', if(@currentField == 'C', '#aa66cc', if(@currentField == 'D', '#bbdefb', if(@currentField == 'E', '#cddc39', '' )))))"
       }
    }

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.