# If statement with dates

✭✭✭✭

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.

Tags:

• ✭✭✭✭✭✭

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")))

• ✭✭✭✭

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?

• ✭✭✭✭✭✭

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

• ✭✭✭✭

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

• ✭✭✭✭

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.

• ✭✭✭✭✭✭

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")))

• ✭✭✭✭

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

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!