One of my duties at work is to put together a calendar of various due dates every quarter. Of course, I could manually put the day numbers in, but where’s the fun in that?! The trickiest part in getting Excel to do this automatically is getting the first of the month on the right day. Most people would jump straight to multiple IF() functions, but there is a much more elegant way using the CHOOSE() function.
The syntax is: CHOOSE(Index, Value1, Value2, Value3…) The function uses the Index to return a value from the list of one to 254 value arguments. For example, if Index is 2, the second value in the list is returned. We’ll use the CHOOSE() function for each of the day number cells in the first week (A4 to G4). Let’s break it down into the possible desired outcomes: each cell can have one of only three values: blank, if the day is before the 1st; 1, if the day is the 1st, and; the previous day incremented by 1 for any other day, except Sunday.
If you didn’t know, Excel uses integers for dates and 0 is equal to Saturday, January 0, 1900 (yes, Day 0.). To get the next day you add 1, so day 1 is Sunday, January 1, 1900. (Too bad the original programmers didn’t use this day as Day 0 as you will see!) Carrying this methodology forward, July 1, 2016 is really a value of 42,552 which is that many days from day 0. To figure out the day of the week, we use the rarely used function MOD(). This returns the remainder after a number is divided by a divisor.
I’ve input the date for the first of the month in cell D2, so the formula is MOD(D2,7). This divides the value in cell D2 by 7 and returns the remainder which could be 0 through 6. Turns out a remainder value of 0 is Saturday, 1 is Sunday, 2 is Monday and so forth. Using July 1, 2016, MOD(42552,7) = 6 which is Friday. The MOD() function is going to drive the CHOOSE() function’s Index parameter.
All of the day number cells need to use a Custom format so they will just display the day number even though the cell value actually contains a full date value. Just select them, right-click and choose Format Cells…. Select Custom for the Category and enter the letter d in the Type field. Click OK.
Following me so far? Good, let’s get going!
Let’s start with Sunday, cell A4. The full formula is: =CHOOSE(MOD(D2,7)+1,””,D2,””,””,””,””,””). Note I have added a +1 to the MOD() result so now the Index’s range will be 1 to 7, rather than 0 to 6. The Index for the CHOOSE() function cannot be 0 and must be 1 or higher. As such, 1 is now Saturday, 2 is Sunday, 3 is Monday, etc.
Sunday being the first square in the month is sort of an oddball. It can only be blank, if it isn’t the first, or 1 if it is. If Sunday is the first, the Index would be 2. As such, the second value parameter is D2, the first of the month. All other value parameters can be blank (“” –double quotes).
Monday, is a little trickier. The full formula is: =CHOOSE(MOD(D2,7)+1,””,D2+1,D2,””,””,””,””). Let’s walk through each of the CHOOSE() parameters:
- Index: MOD(D2,7)+1. Results in 1 to 7 depending on the value in cell D2 and corresponding to Saturday to Friday
- Value1: “”. If the first is a Saturday (Index = 1), Monday should be blank
- Value2: D2+1. If the first is a Sunday (Index = 2), Monday would be the 2nd of the month
- Value3: D2. If the first is a Monday (Index = 3), set the value to the first of the month
- Value4 through Value7: “”. If the first is a Tuesday through Friday (Index = 4 to 7), Monday should be blank
Here are the formulas for all of the days:
A4 (Sun): =CHOOSE(MOD($D$2,7)+1,””,D2,””,””,””,””,””)
B4 (Mon): =CHOOSE(MOD($D$2,7)+1,””,D2+1,D2,””,””,””,””)
C4 (Tue): =CHOOSE(MOD($D$2,7)+1,””,D2+2,D2+1,D2,””,””,””)
D4 (Wed): =CHOOSE(MOD($D$2,7)+1,””,D2+3,D2+2,D2+1,D2,””,””)
E4 (Thu): =CHOOSE(MOD($D$2,7)+1,””,D2+4,D2+3,D2+2,D2+1,D2,””)
F4 (Fri): =CHOOSE(MOD($D$2,7)+1,””,D2+5,D2+4,D2+3,D2+2,D2+1,D2)
G4 (Sat): =CHOOSE(MOD($D$2,7)+1,D2,D2+6,D2+5,D2+4,D2+3,D2+2,D2+1)
The cells for the second and following weeks are just 1 plus the preceding day number. You might be wondering why I’m using the date values rather than just plain old integers 1 through 31. The reason is I also want to show the filler dates for the following month at the end, if any. Since I’m using the actual date values, once we hit the next month the number automatically restarts at 1. Pretty nifty, eh? If this didn’t matter to you, you could simplify the above first-week formulas with just the numbers 1 through 7 for the Values, as appropriate, in the various formulas.
If you don’t want the next month’s days, you could just wrap the last week formulas in some IF() functions, like =IF(G16=””,””,IF(G16+1>EOMONTH($D$2,0),””,G16+1)). So if the prior cell is blank, display a blank. If it isn’t a blank, see if it’s the past the last day of the month. If so, show a blank, otherwise add one to the prior day value. That said, a less elegant method, but simpler way would be to just delete the formulas for days in the following month.
I hope you enjoyed this little tutorial and will find other uses for the CHOOSE() function. I use it for many things like adding the quarter number (e.g., Q1, Q2, etc.) based on the month. The MOD() function can come in handy in certain unique circumstances where you have many sets of repeating columns or rows.
If you don’t feel like typing, you can download the Excel file here.