Calc a beginning of week as Monday
Hello. youd think this would be a simple calculation, and Im finding it works every day but on Sunday. currently using =[Created Date Only]@row  (WEEKDAY([Created Date Only]@row)  2) to calculate the starting day of the week as Monday. Ideas on modifying it so that Sunday will calc as the previous Monday?
Thanks
Best Answer

Hi @TimMelton
Background
The WEEKDAY function will return a number between 1 and 7 (1 being Sunday, 7 being Saturday).
So WEEKDAY  2 will return a number between 1 and 5 (12 being 1, 72 being 5).
If you want Monday to be day 1 then you can use WEEKDAY 1 to create a range 0 to 6 (1 being Monday, 6 being Saturday)
And then add an IF function to cope with Sunday (that will be 0 and should be 7).
The formula would be
=IF(WEEKDAY([Created Date Only]@row)  1 = 0, 7, WEEKDAY([Created Date Only]@row)  1)
This says if the WEEKDAY minus 1 is equal to 0 then return 7, otherwise return the weekday 1.
Your formula
In your case you appear to be subtracting the weekday from the created date which will return the date of the previous Monday. You can use the same logic as above. Instead of subtracting the weekday minus 2 you subtract 6 if the weekday minus 2 is 1. Like this:
=[Created Date Only]@row  (IF(WEEKDAY([Created Date Only]@row)  2 = 1, 6, WEEKDAY([Created Date Only]@row)  2))
Answers

Hi @TimMelton
Background
The WEEKDAY function will return a number between 1 and 7 (1 being Sunday, 7 being Saturday).
So WEEKDAY  2 will return a number between 1 and 5 (12 being 1, 72 being 5).
If you want Monday to be day 1 then you can use WEEKDAY 1 to create a range 0 to 6 (1 being Monday, 6 being Saturday)
And then add an IF function to cope with Sunday (that will be 0 and should be 7).
The formula would be
=IF(WEEKDAY([Created Date Only]@row)  1 = 0, 7, WEEKDAY([Created Date Only]@row)  1)
This says if the WEEKDAY minus 1 is equal to 0 then return 7, otherwise return the weekday 1.
Your formula
In your case you appear to be subtracting the weekday from the created date which will return the date of the previous Monday. You can use the same logic as above. Instead of subtracting the weekday minus 2 you subtract 6 if the weekday minus 2 is 1. Like this:
=[Created Date Only]@row  (IF(WEEKDAY([Created Date Only]@row)  2 = 1, 6, WEEKDAY([Created Date Only]@row)  2))

@KPH thank you, that fixed it, and appreciate the background.

Here is what I use for this one:
=Date@row + (2  WEEKDAY(Date@row))  IF(WEEKDAY(Date@row) = 1, 7, 0)
All you need to do is take your original formula and then use an IF to say that if the date is a Sunday, then subtract a week from it (to put it into the previous week).
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 123 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!