Formula to return Monday from a week a date was on

Mil05006

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)

Mil05006

Figured it out-

=IF(WEEKDAY(Date@row) > 4, Date@row - (WEEKDAY(Date@row) - 2), Date@row - (WEEKDAY(Date@row) - 2))

Marcela

Hi @Mil05006!

Glad to know you worked it out. Thank you for posting your solution!

Cheers,

Mil05006

Thanks! Also, this works as a shortened version-

=Date@row - (WEEKDAY(Date@row) - 2)