Help with text formula based on date range

Options

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

Tags:

Answers

  • Craig H
    Craig H ✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Options

    Hey @Craig H - thanks for posting your solution!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!