Date chaging to start of the week formula

Lubica Zummerova

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!

Paul Newcome

It looks like you are mixing commas and semicolons as your delimiters in between sections. It is going to be one or the other depending on your region.

=Start3 + IF(WEEKDAY(Start3) = 1, 1, IF(WEEKDAY(Start3) = 2, 0, IF(WEEKDAY(Start3) = 3, -1, IF(WEEKDAY(Start3) = 4, -2, IF(WEEKDAY(Start3) = 5, -3, IF(WEEKDAY(Start3) = 6, -4, IF(WEEKDAY(Start3) = 7, -5)))))))

OR

=Start3 + IF(WEEKDAY(Start3) = 1; 1; IF(WEEKDAY(Start3) = 2; 0; IF(WEEKDAY(Start3) = 3; -1; IF(WEEKDAY(Start3) = 4; -2; IF(WEEKDAY(Start3) = 5; -3; IF(WEEKDAY(Start3) = 6; -4; IF(WEEKDAY(Start3) = 7; -5)))))))

Paul Newcome

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))))))

Lubica Zummerova

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!

Paul Newcome

It looks like you are mixing commas and semicolons as your delimiters in between sections. It is going to be one or the other depending on your region.

=Start3 + IF(WEEKDAY(Start3) = 1, 1, IF(WEEKDAY(Start3) = 2, 0, IF(WEEKDAY(Start3) = 3, -1, IF(WEEKDAY(Start3) = 4, -2, IF(WEEKDAY(Start3) = 5, -3, IF(WEEKDAY(Start3) = 6, -4, IF(WEEKDAY(Start3) = 7, -5)))))))

OR

=Start3 + IF(WEEKDAY(Start3) = 1; 1; IF(WEEKDAY(Start3) = 2; 0; IF(WEEKDAY(Start3) = 3; -1; IF(WEEKDAY(Start3) = 4; -2; IF(WEEKDAY(Start3) = 5; -3; IF(WEEKDAY(Start3) = 6; -4; IF(WEEKDAY(Start3) = 7; -5)))))))

Lubica Zummerova

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?

Paul Newcome

@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.

Lubica Zummerova

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

Thanks a lot Paul!!

Paul Newcome

Happy to help! 👍️