Help with text formula based on date range

I'm trying to create a formula that essentially does this:

If [Due Date]@row occurs next week then "Next Week"

ELSEIF [Due Date]@row occurs last week then "Last Week"

I was initially doing this with WEEKNUMBER, but that doesn't work when today and due date are in different years. I then tried doing date math by comparing due date to TODAY() and then generating the value based on those numeric ranges, but I'm having trouble getting it right because the "range' of this week and next week shifts depending what "today" is.

Thanks for any help!

Craig

Find more posts tagged with

Formulas

Accepted answers

All comments

Craig H

I figured it out

=IF(Status@row = "Complete", "Complete",

IF(ISBLANK([Due Date]@row), "No Due Date",

IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week",

IF(AND(WEEKNUMBER([Due Date]@row) = 1, WEEKNUMBER(TODAY()) = 52), "Next Week",

IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) + 1, "Next Week",

IF(AND(WEEKNUMBER([Due Date]@row) = 52, WEEKNUMBER(TODAY()) = 1), "Last Week",

IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) - 1, "Last Week",

IF([Due Date]@row - TODAY() >= 8, "More than 1 week in future",

IF([Due Date]@row - TODAY() <= -8, "More than 1 week overdue")))))))

Genevieve P.

Hey @Craig H - thanks for posting your solution!

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.