Find the Last Sunday Of the Month

June 29, 2022 - by Bill Jelen

Problem: I have a column of dates. I need to post-date those dates to the last Sunday of the month. Or, I need to pre-date those to the first Monday of the month. Or, pre-date the date to the previous Monday, but only if today isn’t already a Monday. Or, post-date to the next Friday, but only if today isn’t a Friday.

Strategy: Use a formula from the table in this topic.

Excel offers a WEEKDAY function that helps you to identify the weekday of the date.

The function historically offered 3 ways to identify the weekday. The traditional method used 1 for Sunday through 7 for Saturday. The return type of 2 used 1 for Monday and 7 for Sunday. The return type of 3 used 0 for Monday and 6 for Sunday. That last return type was great for calculating the “Week Beginning”. You could use =A2-WEEKDAY(A2,2) and all dates would go back to Monday.

Today, Excel offers 7 additional return types, numbered 11 through 17. Here is the tooltip explaining them all.

The WEEKDAY function returns a numeric code for each weekday. The 2nd argument offers 3 original and 7 new codes. 1 is Sunday=1 through Saturday = 7. An argument of 2 gives you Monday=1 thorugh Sunday=7, an argument of 3 gives you Monday=0 through Sunday = 6. The seven new arguments are 11 through 17, always return 1 through 7. 11 Starts with Monday as 1, 12 is Tuesday as 1, and so on. — Figure 544. WEEKDAY now offers 10 return types.

If you sit down to figure these calculations out, you really have to love the brilliance of the return type 3 with its results of 0 through 6. All of the ones that result in 1 through 7 make your formula much harder.

Lets say that you want to roll a date back to Sunday. The date is in A2. If the date happens to fall on a Monday, the WEEKDAY(A2,11) is 1. So, life is simple, you could use =A2-WEEKDAY(A2,11) to roll the Monday back to Sunday. This logic works throughout the week, all the way up through Saturday. The WEEKDAY(,11) of a Saturday is 6, so =A2-WEEKDAY(A2,11) will subtract 6 from the date and you end up on the prior Sunday. The formula falls apart when A2 already is a Sunday. =WEEKDAY(,11) of a Sunday is 7. When you subtract 7 from the current date, you end up a week too early.

Here you are, with a WEEKDAY function that works 6 out of 7 days. Whenever the WEEKDAY function returns a 7, you need it to be a zero.

Enter the MOD function. Calculate the WEEKDAY, then take the MOD(Weekday(),7). For the numbers 1 through 6, the MOD will be 1 through 6. But for the number 7, the MOD will be 0. Perfect.

For background, MOD stands for MODULO. =MOD(100,7) takes 100, divides by 7, throws out the integer portion, then expresses the remainder as a whole number. 100 divided by 7 is 14 with a remainder of 2. =MOD(100,7) will give you the remainder of 2.

Are you still reading? This gets incredibly complex. Below are two tables showing all of the formulas that you will need. The tables assume that your date is in A2.

For the last Sunday of the month, use =EOMONTH(A2,0)-MOD(WEEKDAY(EOMONTH(A2,0),11,7). Change the 11 to 12 through 17 to get the last Monday through Sunday. — Figure 545. Find the last weekday of the month.

If you need to build “Week of” dates, these formulas will work.

For Week Ending on Sunday, us =A2+(7-WEEKDAY(A2,11)) — Figure 546. Change the date in A2 to a week beginning date.

If you are in Excel 2007 or earlier, then you are limited to the return types of 1, 2, or 3. The following table uses CHOOSE to add or subtract the appropriate number of days.

Before Excel 2010, to find the week ending Sunday, you would have to use =A2+CHOOSE(WEEKDAY(A2),0,6,5,4,3,2,1) and similar functions for other months. — Figure 547. Back in Excel 2007, WEEKDAY offered less options.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jess Bailey on Unsplash