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

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)

Find more posts tagged with

Formulas

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.