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.
- 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.
- Find the last weekday of the month.
If you need to build "Week of" dates, these formulas will work.
- 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.
- Back in Excel 2007, WEEKDAY offered less options.