Calc a beginning of week as Monday

TimMelton

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

KPH

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

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

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

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

TimMelton

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

Paul Newcome

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