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

=A2-WEEKDAY(A2,2)+1

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

=A2-WEEKDAY(A2)+1

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.