Share this content
Tags:

How You Can Determine the First Sunday in a Month in Excel

Dec 9th 2014
Share this content
AccountingWEB

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:

=CHOOSE(WEEKDAY(A1,1),7,6,5,4,3,2,1))

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:

=A1+CHOOSE(WEEKDAY(A1,1),7,6,5,4,3,2,1)

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:

=A1+CHOOSE(WEEKDAY(A1,2),7,6,5,4,3,2,1)

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:

=A1+CHOOSE(WEEKDAY(A1,1),4,3,2,8,7,6,5)

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:

=A1+CHOOSE(WEEKDAY(A1,1),4,3,2,8,7,6,5)

  • 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.

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.