Help with an IF formula

Hello.

I’m looking for help with an IF formula to help track tasks that are overdue / on time / pending etc.

I am trying to combine 2 IF formulas into 1 formula, but it’s only bringing the result back for the first formula. They work separately.

Can anyone help?

If({Status}=“Done / NA”,If({Days Early/Late}<0,“Done Overdue :x:”,If({Days Early/Late}>0,“Done Early :white_check_mark:”,If({Days Early/Late}=0,“Done on Time :white_check_mark:”),IF({Status}<>“Done / NA”,If(rounddown(DAYS(TODAY(),{Due Date}),0)=0,“Due Today :date:”,If(DAYS(TODAY(),{Due Date})<0,“Pending :construction:”,If(DAYS(TODAY(),{Due Date})>0,“Overdue :x:”)))))))

Thank you!

@RachelB

Try this:

IF({Status} = "Done / NA", 
   IF({Days Early/Late} < 0, "Done Overdue ❌ ", 
      IF({Days Early/Late} > 0, "Done Early ✅", "Done on Time ✅")
   ), 
   IF(ROUNDDOWN(DAYS(TODAY(), {Due Date}), 0) = 0, "Due Today 📅", 
      IF(DAYS(TODAY(), {Due Date}) < 0, "Pending 🚧", "Overdue ❌")
   )
)

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: The ITEM ID column - What most people don’t know.
Contact me directly here: Contact – The Monday Man

2 Likes

Hi @JCorrell,

Thank you so much for your help. This works great, thank you!

One question. We have a status column with a ‘Not Started’ option. How do I add to the formula - if Status = ‘Not Started’ and the Days Early - /Late + column = ‘empty’ then put ‘No Date’.

Thanks again!

Rachel

@RachelB

Generally, I try to avoid nested IFs whenever possible. But in this case, it’s probably the easiest.

I don’t completely understand your added condition. But here is the basic idea:

IF(AND({Status} = "Not Started", {Date} = ""), "No Date",  
   IF({Status} = "Done / NA", 
      IF({Days Early/Late} < 0, "Done Overdue ❌ ", 
         IF({Days Early/Late} > 0, "Done Early ✅", "Done on Time ✅")
      ), 
      IF(ROUNDDOWN(DAYS(TODAY(), {Due Date}), 0) = 0, "Due Today 📅", 
         IF(DAYS(TODAY(), {Due Date}) < 0, "Pending 🚧", "Overdue ❌")
      )
   )
)

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: The ITEM ID column - What most people don’t know.
Contact me directly here: Contact – The Monday Man

@JCorrell this worked great! Thank you again for your help with this!

KR

Rachel

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.