I was recently asked if there was an easy way to determine the first Sunday in a month. As you might expect, it can be tedious to manually determine the first Sunday of each month in an expense report spreadsheet. But you can do it more automatically: It’s a simple matter of using the WEEKDAY and CHOOSE functions together.
Let’s first discuss the WEEKDAY function, which returns the day of a week. The function has two arguments:
- Serial_Number: This is any date that Excel can recognize, either a date that you type into a worksheet cell, or entered using the DATE function.
- Return_Type: This optional argument specifies the numbering convention Excel uses. If you specify 1 or omit this argument, Excel returns 1 for Sunday, 2 for Monday, and so on. If you specify 2 for this argument, Excel turns 1 for Monday, 2 for Tuesday, and so on. Other arguments allow you shift the starting day of the week as needed, as shown in Figure 1.
Figure 1: The WEEKDAY function returns the number that corresponds with a 7-day week.
As shown in cell B1 of Figure 2, the WEEKDAY function returns 4 for December 31, 2014. This indicates that the last day of 2014 is a Wednesday, or the fourth day of the week.
The second function we’ll use is the CHOOSE function. The CHOOSE function has up to 255 arguments:
- Index_Num – This is a number that correlates to which value you wish to return.
- Value 1 to Value 254 – These are numeric or text-based values that you wish to return based on the Index_Num.
In our case CHOOOSE is going to return a value that corresponds with a weekday, so we’ll have 7 values that correspond to each day of the week, in this case from 7 down through 1. As shown in cell B2 of Figure 2, this formula returns 4:
Figure 2: The CHOOSE returns a value from a list based on an input, in this case whatever WEEKDAY returns.
It’s coincidental that both WEEKDAY and CHOOSE return 4 in this situation. Since Wednesday is the 4th day of the week, we’ll need to add 4 days to it to return a Sunday.
Cell B3 of Figure 3 brings all of the components together:
Figure 3: The CHOOSE and WEEKDAY functions allow us to craft a formula that returns the first Sunday in a month.
If you type a different month-end date in cell A1, such as 1/31/2015 then cell B3 will return 2/1/2015, as shown in Figure 4. The first Sunday of February 2015 happens to fall on the first of the month.
Figure 4: Entering 1/31/15 in cell A1 causes cell B3 to return the first Sunday in February 2015.
If you want to return the first Monday for a month, you can change the 1 in the WEEKDAY function to 2 as shown in Figure 5:
Figure 5: Change the 1 within the WEEKDAY function to 2 if you wish to return the first Monday in a month.
If you need to return the first say Thursday of a month, you’ll likely find it easiest to modify the CHOOSE function:
Arranging the values within the CHOOSE function can get confusing, so an easy solution is to set up a series of numbers where you can see the day of the week being returned. Carry out these steps to recreate what you see in Figure 6:
- Enter 12/31/14 in cell A1.
- Enter 1/31/15 in cell A2.
- Select cells A1 and A2, and then drag the fill handle down a number of rows to create a series of month-end dates.
- Enter this formula in cell B1:
- Modify the date format in cell B1 to show the day of the week as well as the date:
press Ctrl-1 to display the Format Cells dialog box, choose Date, and then double-click on the Wednesday, March 12, 2012 number format.
- Double-click the Fill Handle in cell B1 to copy the formula down the column.
Any inconsistencies in your CHOOSE values will immediately pop-out, and so you’ll know which Value arguments to modify to calculate the proper day of the week.
Figure 6: Values in the CHOOSE function can be reordered to return the first Thursday in a month.
About the author:
David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.