Dynamic "Week Of"

kira11

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.

kira11

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]#

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

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

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

kira11

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]#