If statement with dates

Amanda Jeske

Hi there,

I am trying to put a formula in a symbol column (Yes, Hold, No options) and want the formula to make the symbol for "Hold" if the due date is within 14 days of TODAY. This is the formula I have come up with but I am not getting the "Hold" portion

=IF([Due Date]@row < TODAY(), "No", IF([Due Date]@row < TODAY(-14), "Hold", IF(Status@row = "Complete", "Yes", "")))

Anyone have thoughts on that? I am sure my error is in the TODAY(-14) section but not sure what would fix it.

I appreciate your help

Paul Newcome

The problem is in the order of the arguments. Nested IF statements work from left to right and stop at the first true statement.

So if a date is less than today (which also includes dates less than 14 days ago), then it will trigger. Basically you will want to rearrange your statements so that the Status argument comes first. Then less than -14 days. Then less than today.

=IF(Status@row = "Complete", "Yes", IF([Due Date]@row < TODAY(-14), "Hold", IF([Due Date]@row < TODAY(), "No")))

Amanda Jeske

Paul, this worked! Thank you so much. Another follow up question I have - If I want the result to be "Hold" for a period of time before the due date would you have an idea on how to do that? Say 1 day to 14 days before due date?

Paul Newcome

I'm not sure I follow. Isn't that what the above formula should be doing?

Amanda Jeske

So that was making everything that wasn't past due or Complete, "Hold".

Amanda Jeske

I feel like I need an IF(AND statement but not sure how best to do that. I want tasks that are more than 14 days away from due to be blank and have no flag.

Paul Newcome

Let me make sure I understand correctly...

If the Status is complete then "Yes".

If the Due Date is more than 14 days away, blank.

If the Due Date is coming up in the next 14 days, "Hold".

If the due Date is in the past, "No".

Is the above correct? If so...

=IF(Status@row = "Complete", "Yes", IF([Due Date]@row > TODAY(14), "", IF([Due Date]@row >= TODAY(), "Hold", "No")))

Amanda Jeske

Paul, you are a genius!!!!!!! Thank you so much that works perfectly!

Paul Newcome

Happy to help. 👍️