Date chaging to start of the week formula
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!
Best Answer
-
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)))))))
Answers
-
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))))))
-
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!
-
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)))))))
-
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?
-
@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.
-
Oh of course! My column was not set in date format, now it all makes sense!
Thanks a lot Paul!!
-
Happy to help! 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!
