compound if formula

Platform discussions
, ,
1

Hi
I wrote the formula below to checks the status of moderation based on the ‘Moderation Completed’ and ‘Moderation Due Date’ columns. If ‘Moderation Completed’ is ‘Yes’, it returns ‘Completed’. If the due date is past and ‘Moderation Completed’ is ‘No’, it returns ‘Overdue’. If the due date is today or in the future and ‘Moderation Completed’ is ‘No’, it returns ‘Date yet to come’. Otherwise, it returns nothing.
It currently returns invalid formula, I really appreciate it if anyone could help me understand why it is not working and how to fix it, thank you
IF({Moderation Completed} = “Yes”, “Completed”,
IF(AND(FORMAT_DATE({Moderation Due Date}, “DD/MM/YYYY”) < FORMAT_DATE(TODAY(), “DD/MM/YYYY”),{Moderation Completed}=“No”), “Overdue”, IF(AND (FORMAT_DATE({Moderation Due Date}, “DD/MM/YYYY”) >= FORMAT_DATE(TODAY(), “DD/MM/YYYY”),{Moderation Completed}=“No”), “Date yet to come”, “” )))

2

The issue with your formula appears to be in how you’re comparing dates. When comparing dates in monday.com formulas, you should use the actual date values rather than formatting them as strings first.

Try this:

IF({Moderation Completed} = "Yes", "Completed",
  IF(AND({Moderation Due Date} < TODAY(), {Moderation Completed} = "No"), "Overdue", 
    IF(AND({Moderation Due Date} >= TODAY(), {Moderation Completed} = "No"), "Date yet to come", "")))

The key changes to your formula:

  1. Removed the FORMAT_DATE() functions - comparing formatted date strings can lead to incorrect results

  2. Directly compared the date values using < and >= operators

  3. Ensured all parentheses are properly matched

Let me know if that helps.