Need Help with Approaching Due Date Formula

I have an Approaching Due Date column with the Star checkbox and I want to create a formula that will show the Star if today is between 7 working days in advance of the End Date and the End Date, and the Status is NOT Complete. I also have a column for Past Due with a Red Flag checkbox. I want the Approaching Due Date formula to exclude any tasks that are already marked as Past Due.

Currently, I have this formula in place (which is close to what I'm trying to do, but doesn't have the exclusion of Past Due line items) -

=IF(AND([End Date]@row <= TODAY(7), NOT(Status@row = "Complete")), 1, 0) 

How can I write this to achieve the outcome described above?



Best Answer

  • Rochelle_B
    Rochelle_B ✭✭✭
    edited 07/19/22 Answer ✓

    Hi Marcy! You're missing a few arguments in your formula. Give this one a shot:

    =IF(AND([End Date]@row <= TODAY(7), [End Date]@row >= TODAY(), [Past Due Flag]@row = 0, NOT(Status@row = "Complete")), 1, 0)

    Stay Agile,


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!