Calc a beginning of week as Monday

Options
✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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 (1-2 being -1, 7-2 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.

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

• ✭✭✭✭✭✭
Options

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 (1-2 being -1, 7-2 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.

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!