# Date chaging to start of the week formula

Options

Hi all,

I was wondering if there is a formula that can take my start date that occurs at any random day of the week in column A (lets say tasks starts on a Tuesday 25/02/2020) and give me a date of the start of the week in column B (Monday 24/02/2020)?

Thank you!

• ✭✭✭✭✭✭
Options

The most straightforward way would be to build a nested IF.

To pull the weekday, you would use

WEEKDAY([Date Column]@row)

Then you say that if that number equals x then add y. The goal is that you want x + y = 2 (Monday's weekday).

So if the date is a Monday (weekday = 2) then you would add 0. Tuesday (weekday = 3) you would add - 1.

Assuming you want Saturday to go to the previous Monday and Sunday to go to the next Monday, it would go something like this.

=[Date Column]@row + IF(WEEKDAY([Date Column]@row) = 1, 1, IF(WEEKDAY([Date Column]@row) = 2, 0, IF(WEEKDAY([Date Column]@row) = 3, -1, IF(WEEKDAY([Date Column]@row) = 4, -2, IF(WEEKDAY([Date Column]@row) = 5, -3, IF(WEEKDAY([Date Column]@row) = 6, -4, -5))))))

• Options

Hi Paul,

Thank you, I tried it but for some reason it still shows at #UNPARSEABLE. I have included a screenshot in case I have done something wrong.

Any idea what could be causing it?

Thank you!

• Options

Hi Paul,

Unfortunately it still doesn't work. The formula with commas says "INVALID COLUMN VALUE" and the one with semicolons still shows as "UNPARSEABLE". Any idea?

• ✭✭✭✭✭✭
Options

@Lubica Zummerova No worries. This is actually one step closer in the right direction. Based on the two different errors, that at least tells us we need to use commas. This also gives us a clue for some trouble shooting based on the #INVALID COLUMN VALUE error.

Make sure that the column you are putting this formula in is a date type column as the formula will generate a date.

• Options

Oh of course! My column was not set in date format, now it all makes sense!

Thanks a lot Paul!!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!