I am trying to make the text say Expired or Current compared to a date column OR stay blank if the date column is blank. So far, I have:

"txtContent": "=if([$ExpiryDate]) == '',  '' , if([$ExpiryDate] <= @now, 'Expired', if([$ExpiryDate] > @now, 'Current', '')))"

But for whatever reason, the column then shows the ENTIRE formula in the column instead. I have tried several different permutations of the above, including isblank, but I just can't seem to get the syntax exactly how SharePoint likes it.

  • I took that offending second bracket out, but now it just makes everything not Current to Expired, even if date column is blank. – ichinap Jul 10 '20 at 4:27

You can use Number(DateTimeColumn) == 0 to check if the date column is blank or not. This is the major hint, rest you can build the JSON code around it according to your other conditions.

So, try something like this:

=if(Number([$ExpiryDate]) == 0, '' , if([$ExpiryDate] <= @now, 'Expired', 'Current'))


Make a column no background colour if no value

  • 1
    Excellent, thank you! I didn't know you could check date columns using Number, that's so helpful. – ichinap Jul 10 '20 at 7:40
  • Great, glad it helped you. Please Upvote(^) and accept as an Answer as it helped you. – Ganesh Sanap Jul 10 '20 at 7:49

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.