0

I am trying to format a column with a complex nested if statement but the column is ignoring the first if statement and only seeing the second. I have been trying to tweak this, but I can't get it to work. It looks correct to me.

If Resubmitted (Date/Time) is empty and Submitted(date/Time) + 45 days is less than or equal to today, then color field red, else if Resubmitted is not empty and Resubmitted +45 is less than or equal to today, then color field orange. The second half of the if statement works setting the background to orange. But the data in the fields that should be either true on the first IF, or false on the first IF and false on the second IF are disappearing when this JSON is added.

Anyone with another pair of eyes, please help and thank you in advance.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(Number([$Resubmitted])==0 && ([$Submitted]+3888000000) <= @now,'#ff0000',if(Number([$Resubmitted])!=0 &&([$Resubmitted]+3888000000) <= @now, '#ffa500',''))"
  }
}

Dates missing from the two submitted columns with blank Resubmitted columns

enter image description here

Improve this question

1 Answer 1

Reset to default
1

In JSON Formatting, you can check if Date & Time column is blank or not like below:

Number([$Resubmitted]) == 0

Add this in your condition and it should work for you.

You can find other ways to check if date & time column is empty or not at: SharePoint JSON formatting: Check if date & time column is blank/empty

GitHub sample for reference: Formatting a column when a date column is blank

Updates from comments:

Try using below JSON code & see if it works for you:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(Number([$Resubmitted]) == 0 && [$Submitted] <= @now - 3888000000, '#ff0000', if(Number([$Resubmitted]) != 0 && [$Resubmitted] <= @now - 3888000000, '#ffa500', ''))"
  }
}

Output:

enter image description here

Improve this answer
6
  • Thank you, Ganesh, but because it is Date/Time, to check for blank you have to convert it to a string value. I did try your number conversion, though, and it did not work. :( Commented Mar 16, 2021 at 14:14
  • No need to convert it to a string value. Number(Date) == 0 works fine for me. See screenshot attached in updated answer. Check if there is any other logical problem with your JSON. Commented Mar 16, 2021 at 18:00
  • I decided to copy and paste your JSON in because I am not seeing any difference in logic besides missing parentheses and I am still not getting the correct outcome with the red. It is actually making the fields that should be true for the first if or false for first if and false for second if either blank or causing the font to turn white. See the screenshots. Commented Mar 16, 2021 at 18:21
  • I also added the color attribute just to check the font but it is actually not the font causing the field data to go away. I have looked and looked at this. See the screen shot I added in the edit. Commented Mar 16, 2021 at 18:44
  • 1
    Oh, thank you so much. I originally had it that way with the @now - 3888000000 but obviously did not have the syntax correct. Thank you for all of your help. Microsoft duplicated the issue and I was waiting to hear back from them. Commented Mar 17, 2021 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.