Let’s say you have a weekly roster. You have a date and you need to identify the Monday start date for the week that contains that date. The formula is fairly easy.
Check out the table below.
I have used the Long Date format from the middle of the Home ribbon to format all columns.
There are dates entered in column A.
Cell B2 has the following formula
This has been copied down the column.
The WEEKDAY function returns a number representing the day of the week for a date.
Using the ,2 on the end of the function means Monday = 1 and Sunday =7.
So if we deduct a date’s WEEKDAY value (using ,2 on the end) from itself, we always end up with the preceding Sunday. So adding 1 gives you the preceding Monday or the same Monday if the day is a Monday.
If you wanted to find the Sunday week start date (as per column C), cell C2 has the following formula
This uses the default WEEKDAY function (no ,2 on the end) which returns Sunday = 1 and Saturday = 7. Deducting a date’s WEEKDAY result from the date always gives you the preceding Saturday and adding one converts it to the Sunday.