"Week of" formula with Friday as start of week
Need the formula below to recognize that the beginning of the week is a Friday. If the "Date of Shift" lands on a Friday, then the "Week of" should be the same date.
=IF(WEEKDAY([Date of Shift]@row) = 7, [Date of Shift]@row - 1, [Date of Shift]@row - (WEEKDAY([Date of Shift]@row) + 1))
Below is a screenshot of the formula in action, working properly except when the shift date lands on a Friday.
In the example below, if the date of shift is 1/17/2025, the "Week of" should be 1/17/2025. Our shifts are tracked Friday through Thursday.
THANK YOU IN ADVANCE!
Answers
-
We figured out a work around to the problem above by using a formula for each day of the week individually and writing the formula to display the date we wanted. Here's the updated formula understanding that Sunday is recognized in SmartSheet as day 1:
Here's the formula in action:
If you have a more simplified way to condense the formula above its much appreciated!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!