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)




Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!