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

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

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. :( – GoGorilla Mar 16 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. – Ganesh Sanap Mar 16 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. – GoGorilla Mar 16 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. – GoGorilla Mar 16 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. – GoGorilla Mar 17 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.