Calculate the First Day and Last Day of the Week

Excel’s WEEKNUM function can return the specific week that a date falls in. But to do the reverse is a bit more challenging. In this post, I’ll show you how you can get the first and last day of a week (as well as anything in-between).

Setting up some variables

You can make this into a large and complex formula, but I’m going to make it a bit more organized by utilizing named ranges. The two names ranges I’m going to set up are for the day of the week (DAYNUMBER) that I want to calculate for, and the first day of the year (FIRSTDAY).

I’m going to use Monday as the day of the week my week starts on. On my regional settings, that is weekday #2. If you’re not sure about yours, you can use the WEEKDAY function on a day that is a Monday (or whichever day you wish to use) to determine the number associated with that.

Calculating the difference between the first day and your desired day of the week

The day the year begins on serves as an important starting point. This year began on a Saturday. If my desired day is Monday, then I need to calculate the difference between those days of the week. The formula for that would be as follows:

=DAYNUMBER - WEEKDAY(FIRSTDAY)

This returns a value of -5. If I wanted to know when the first Monday of the year was, I couldn’t just deduct 5 from the first day or I’d end up in the wrong year. What I need to do is to set up an IF function to say that if the difference is negative, I will add 7 to adjust for that fact. And if it isn’t negative, then I can just add to the starting date. Here is my formula thus far:

=IF(DAYNUMBER-WEEKDAY(FIRSTDAY)<0,DAYNUMBER-WEEKDAY(FIRSTDAY)+7,DAYNUMBER-WEEKDAY(FIRSTDAY))

To get to the right day, I need to add this to my starting date:

=FIRSTDAY+IF(DAYNUMBER-WEEKDAY(FIRSTDAY)<0,DAYNUMBER-WEEKDAY(FIRSTDAY)+7,DAYNUMBER-WEEKDAY(FIRSTDAY))

Using the above formula, Excel tells me that Jan. 3, 2022, was the first Monday of the year, which is correct. But I need to adjust the formula to ensure the calculation puts me in the correct week.

Adjusting for the week number

The above formula works if I want the first week. If I want it to be more flexible than that, I need to include the week number in my calculation. For that, I’m going to create a named range called WEEK. The key is in adjusting the +7 calculation. In the first argument of my formula, when it was negative, I added 7. If I want the second week, then I need to add it by another factor of 7. Here’s how that part of the formula would look:

WEEK-WEEKDAY(FIRSTDAY)+(7*WEEK)

I also need to add that part to the second argument, which currently doesn’t adjust for the week number:

WEEKDAY(FIRSTDAY)+(7*(WEEK-1))

The completed formula is as follows:

=FIRSTDAY+IF(DAYNUMBER-WEEKDAY(FIRSTDAY)<0,DAYNUMBER-WEEKDAY(FIRSTDAY)+(7*WEEK),DAYNUMBER-WEEKDAY(FIRSTDAY)+(7*(WEEK-1)))

Now I can adjust the calculation for different days of the week and different week numbers. And so whether you’re looking at the first day of the week or the last day of the week, you can just adjust the day number you’re looking for.

Here’s what the formula would look like without named ranges if the year was the current year and it was pulling the Monday of the 50th week of the year:

=(DATE(YEAR(NOW()),1,1)+IF(2-WEEKDAY(DATE(YEAR(NOW()),1,1))<0,2-WEEKDAY(DATE(YEAR(NOW()),1,1))+(7*50),2-WEEKDAY(DATE(YEAR(NOW()),1,1))+(7*(50-1))))

If you liked this post on How to Calculate the First Day and Last Day of the Week, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.