How to check a box if a date is in the past 7 days & next 14

Matthew_Lanterman

Hello,

I want to check a box if the following criteria are met. I want to then base an automation to send out rows that are not complete and were due in the last 7 days / due in the next 14.

Criteria:

Status is not Complete

Finish is in the past 7 days (overdue)

Finish is in the next 14 (look forward 2 weeks)

This is the formula I tried - and is obviously incorrect. Any guidance would be appreciated!

=IF((Status:Status<> "Complete", And(Finish:Finish <=TODAY(-7)),Finish:Finish <TODAY(+14),1,0)

Andrée Starå

Hi @Matthew_Lanterman

I hope you're well and safe!

Not completely sure I follow but try something like this.

=IF(AND(Status@row = "", Finish@row = ""), "", IF(AND(Status@row <> "Complete", Finish@row <= TODAY(-7), Finish@row < TODAY(14)), 1))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Matthew_Lanterman

Hi @Andrée Starå - thank you for the help.

The goal is to surface all tasks that are incomplete within a timeframe (measuring from today, I need lines that are not complete and were due the past 7 days and due in the next 14).

Alternatively, I could write conditions in an automation that might accomplish the same goal:

That way I would not need a formula, and can use the conditions in an automation. Do you think that would work?