How can I calculate date in Microsoft Lists?

Question

I have Lists that create by Microsoft Lists. in the column have "last activity date" I need to highlight item row that after 60 days from "last activity date". About conditional formatting rule have "is after" + "today" but cannot specific x day after that. I workaround by create new column for calculate [last activity date]-today(). but it not success. Please suggest solution. Thank you

Answer 1

Hi @Nattawut Teerajarukul ,

I’m sorry you aren’t feeling good. I’m here if you need anything.

"It show error not support JSON format", can you provide me with a screenshot of the error report and the steps you have taken to set it up before that?

Given that you have already used the conditional formatting rule before, we recommend that you use the column formatting so that you don't have a conditional conflict that overwrites the color of the previous conditional formatting rule. Except instead of highlighting item rows, it's a single column.

Here is my JSON codes:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField <= @now - 5184000000, 'red', '')"
  }
}

Here are steps:

My test result:

Hope the information can help you. And if there have any unclear or misunderstanding, please feel free to post back and we’ll continue to help you all the time!

---

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.

Answer 2

Hi @Nattawut Teerajarukul ,

Per my test you could use view formatting with if fuction @now keyword to change the row background color based on "last activity date" column. Click All items > Format current view > Advanced mode > Type your JSON code > Save

So here is my JSON codes:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if([$lastactivitydate] < @now - 5184000000, 'sp-field-severity--severeWarning', '')"
}

My test result:

---

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.