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
-
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
-
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!
-
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/functionsYou can get a copy of the Smartsheet "Formulas Handbook Document" here
https://app.smartsheet.com/folders/wxrX5xmQJMP7v92q8HV3M6P8gvgW4GGv5H7VjRp1Smartsheet Formula Handbook Dashboard
https://app.smartsheet.com/dashboards/mJ4gJgP984Jm7CfgWChgjrHRhRW8MHwP9x8hW5J1Cheers!
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!