# 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

• ✭✭✭
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

• ✭✭✭
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

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

• ✭✭✭
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!