Helper column to differentiate current week from following week

Hello,

I have determined a way to identify the day of the week via a helper column formula with additional helper columns for the current week. What I want is a way to differentiate the current week from the following/next week. This is strictly Monday - Friday Business days. I will post below my current situation. Please let me know what I can do to separate these from each other. Thank you all!

Day of the week Formula: =IF(WEEKDAY([Start Date(訪問開始日)]@row) = 2, "Monday", IF(WEEKDAY([Start Date(訪問開始日)]@row) = 3, "Tuesday", IF(WEEKDAY([Start Date(訪問開始日)]@row) = 4, "Wednesday", IF(WEEKDAY([Start Date(訪問開始日)]@row) = 5, "Thursday", IF(WEEKDAY([Start Date(訪問開始日)]@row) = 6, "Friday")))))

Current Week: =IF(WEEKNUMBER([Start Date(訪問開始日)]@row) = WEEKNUMBER(TODAY()), 1)

[FORMULA IN QUESTION]

Following Week: =IF(OR(WEEKNUMBER([Start Date(訪問開始日)]@row) = WEEKNUMBER(TODAY()), WEEKNUMBER([Start Date(訪問開始日)]@row) = WEEKNUMBER(TODAY()) + 1), 1)

Tags:

Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Answer ✓

    I think you basically have it. To see if it's next week try:

    =IF(WEEKNUMBER([Start Date(訪問開始日)]@row) = WEEKNUMBER(TODAY(7)), true)

    Or do a combined formula in one column that indicates "This Week" or "Next Week" or "Other"

    =IF(WEEKNUMBER([Start Date(訪問開始日)]@row) = WEEKNUMBER(TODAY(7)), "Next Week", IF (WEEKNUMBER([Start Date(訪問開始日)]@row) = WEEKNUMBER(TODAY()), "This Week", "Other Week"))

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Answer ✓

    I think you basically have it. To see if it's next week try:

    =IF(WEEKNUMBER([Start Date(訪問開始日)]@row) = WEEKNUMBER(TODAY(7)), true)

    Or do a combined formula in one column that indicates "This Week" or "Next Week" or "Other"

    =IF(WEEKNUMBER([Start Date(訪問開始日)]@row) = WEEKNUMBER(TODAY(7)), "Next Week", IF (WEEKNUMBER([Start Date(訪問開始日)]@row) = WEEKNUMBER(TODAY()), "This Week", "Other Week"))

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!