Excel Formula Week Starting with a Monday

The WEEKDAY function has options

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.


Please note: I reserve the right to delete comments that are offensive or off-topic.