Need Help with Approaching Due Date Formula

Options

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?

Thanks!

Marcy

Best Answer

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

    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,

    agilizeconsulting.com

Answers

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

    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,

    agilizeconsulting.com

  • Marcy Burkett
    Options

    Hi Rochelle! Thank you SO much!! With one small edit, this worked like a charm. So appreciate your help. 😀

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

  • Rochelle_B
    Rochelle_B ✭✭✭
    Options

    Good deal!! I'm glad it worked out!


    Stay Agile,

    agilizeconsulting.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!