Dynamic "Week Of"

Hello,

Is there a way/formula I could write to get a single cell to display a dynamic "Week of XX/XX" regardless of what day today is?

I want to display it for a report that will always be the previous Monday-Sunday and will roll forward the next Monday.

Best Answer

  • kira11
    kira11 ✭✭✭
    Answer ✓

    Thanks all for the suggestions, I was able to combine them and tweak Leela's formula as a Sheet Summary metric:

    =IF(WEEKDAY(TODAY()) = 1, TODAY() - 6, IF(WEEKDAY(TODAY()) = 2, TODAY() - 7, IF(WEEKDAY(TODAY()) = 3, TODAY() - 8, IF(WEEKDAY(TODAY()) = 4, TODAY() - 9, IF(WEEKDAY(TODAY()) = 5, TODAY() - 10, IF(WEEKDAY(TODAY()) = 6, TODAY() - 11, IF(WEEKDAY(TODAY()) = 7, TODAY() - 12))))))) then "Week of " + [Previous Monday]#

Answers

  • Leela Lodhi
    Leela Lodhi ✭✭✭

    Hi @kira11,

    To dynamically display the Previous Monday and Sunday, you can follow these steps:

    Step 1: Create Two Helper Date Columns
    Helper Column for Monday (Previous Monday):

    Add this formula in the Monday helper column:
    =IF(WEEKDAY(TODAY()) = 1, TODAY() + 1, IF(WEEKDAY(TODAY()) = 2, TODAY(), IF(WEEKDAY(TODAY()) = 3, TODAY() - 1, IF(WEEKDAY(TODAY()) = 4, TODAY() - 2, IF(WEEKDAY(TODAY()) = 5, TODAY() - 3, IF(WEEKDAY(TODAY()) = 6, TODAY() - 4, IF(WEEKDAY(TODAY()) = 7, TODAY() - 5)))))))

    Helper Column for Sunday (Previous Sunday):

    Add the following formula in the Sunday helper column:
    =IF(WEEKDAY(TODAY()) = 1, TODAY(), IF(WEEKDAY(TODAY()) = 2, TODAY() + 6, IF(WEEKDAY(TODAY()) = 3, TODAY() + 5, IF(WEEKDAY(TODAY()) = 4, TODAY() + 4, IF(WEEKDAY(TODAY()) = 5, TODAY() + 3, IF(WEEKDAY(TODAY()) = 6, TODAY() + 2, IF(WEEKDAY(TODAY()) = 7, TODAY() + 1)))))))

    Step 2: Combine Both Dates
    In another cell, combine the values from the helper columns using this formula:

    ="Week of " + LEFT([Previous Monday]@row, 5) + " - " + LEFT([Previous Sunday]@row, 5)

    Step 3: Hide Helper Columns
    You can hide both helper columns (Previous Monday and Previous Sunday) to keep your sheet clean and display only the combined date range.

    Hope this helps!

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    Hi, @kira11.

    Here's an approach that does not require a helper column.

    =WEEKNUMBER(IF(WEEKDAY(anyDate@row) = 1, anyDate@row - 6, anyDate@row - (WEEKDAY(anyDate@row) - 2)))

    EXPLANATION

    Since your weeks are Monday-Sunday, for any date...

    If it is a Sunday, then return the WEEKNUMBER for the previous Monday ("6 days ago from Sunday" expressed as anyDate@row - 6 ).

    Otherwise, find the date for Monday of that week and return the WEEKNUMBER of said date (anydate@row - (WEEKDAY(anyDate@row)-2)).

    You can find documentation for Smartsheet functions here
    https://help.smartsheet.com/functions

    You can get a copy of the Smartsheet "Formulas Handbook Document" here
    https://app.smartsheet.com/folders/wxrX5xmQJMP7v92q8HV3M6P8gvgW4GGv5H7VjRp1

    Smartsheet Formula Handbook Dashboard
    https://app.smartsheet.com/dashboards/mJ4gJgP984Jm7CfgWChgjrHRhRW8MHwP9x8hW5J1

    Cheers!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    When you say "Week of XX/XX", what would go in place of the Xs?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • kira11
    kira11 ✭✭✭
    Answer ✓

    Thanks all for the suggestions, I was able to combine them and tweak Leela's formula as a Sheet Summary metric:

    =IF(WEEKDAY(TODAY()) = 1, TODAY() - 6, IF(WEEKDAY(TODAY()) = 2, TODAY() - 7, IF(WEEKDAY(TODAY()) = 3, TODAY() - 8, IF(WEEKDAY(TODAY()) = 4, TODAY() - 9, IF(WEEKDAY(TODAY()) = 5, TODAY() - 10, IF(WEEKDAY(TODAY()) = 6, TODAY() - 11, IF(WEEKDAY(TODAY()) = 7, TODAY() - 12))))))) then "Week of " + [Previous Monday]#

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!