Forum Discussion

Imi_Jay's avatar
Imi_Jay
Brass Contributor
Jul 18, 2023

Microsoft List formula is not working

Hi all, I am encountering a problem in a calculated formula in the Microsoft List. Scenario- Based on different values in different columns , I need a record to appear as "Overdue" Formula u...
Lists
microsoft lists
SharePoint Online
SvenSieverding's avatar
SvenSieverding
Bronze Contributor
Jul 18, 2023

Hi Imi_Jay ,

instead of using a calculated column you can also use JSON list formatting to achieve this effect.
That will recalculate automatically.

I translated your formula into this JSON (I hope i got the logic right)

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if([$DueDate]=='Missing info','Missing info',if(((Number([$DueDate])<=Number(@now)) && (toString([$ResultOutcome]) == '') && (toString([$DateResultMarked]) == '')) || ((Number([$DueDate])<=Number(@now)) && [$Require2ndAttempt] && (toString([$ResultOutcome]) == '') && (toString([$DateResubmissionPassed]) == '')),'OVERDUE',''))"
}

 
Just select your column and then select "Column Settings" and then "Format this column"

 


Switch to "Advanced Mode " at the bottom of the new dialog

 


Now paste the JSON formatting into the editor and save

 

Best Regards,
Sven

 

sbocknek's avatar
sbocknek
Copper Contributor
Aug 16, 2023
Hey Sven,

This didn't work for me:

After following the steps, the column simply showed the entire formula as a text string (rather than executing it).

Do you know how to fix this, so that the formula actually executes? Thanks!
  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Aug 17, 2023

    sbocknek Make sure you did not miss the equal to ( = ) sign at the start of this expression: 

     

    =if([$DueDate]=='Missing info','Missing info',if(((Number([$DueDate])<=Number(@now)) && (toString([$ResultOutcome]) == '') && (toString([$DateResultMarked]) == '')) || ((Number([$DueDate])<=Number(@now)) && [$Require2ndAttempt] && (toString([$ResultOutcome]) == '') && (toString([$DateResubmissionPassed]) == '')),'OVERDUE',''))

     

    Also, use the correct internal names of your SharePoint columns. You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?  

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Resources