Help with text formula based on date range

Craig H

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

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!