Formula to return Monday from a week a date was on
Hello,
I'm looking to put together a formula that will pull the Monday a date was on so I can then pull things that were 1 week ago, 2 weeks ago, etc. to build out a report for weekly hours done by members of a team. I've tried some other methods to try and do this but they broke on the year change because Smartsheet just reset the count at 1 on the first week of 2024 and doesn't recognize that week 52 of 2023 was the last week.
So I figured I could try to just pull the Monday from the week a date was on, and it WORKS, except for the first few days of the month. Any theories on how to fix this/what formula to use to make this work? I'm open for a complete rework here as well. For some reason days 1-3 and day 31 break, depending on when they fall in the week. I can put in error handling but it will only work if I use +6 or +7 as the number value which then just gives me the wrong date for these cells.
Formula-
=DATE(YEAR(Date@row), MONTH(Date@row), DAY(Date@row) - WEEKDAY(Date@row) + 2)
Answers
-
Figured it out-
=IF(WEEKDAY(Date@row) > 4, Date@row - (WEEKDAY(Date@row) - 2), Date@row - (WEEKDAY(Date@row) - 2))
-
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks! Also, this works as a shortened version-
=Date@row - (WEEKDAY(Date@row) - 2)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!