How to Quickly Create Calendar in Excel Using One Formula
Many analytical processes are based on certain events. For example, if bonuses are paid on the second Friday of each month, it’s essential to know the exact date for that operation.
Using the formula described in this article, you can create a dynamic table that will automatically populate with the necessary dates based on specific user conditions.
How to Get the Date by Weekday Number in Excel
The image below shows a dynamic table with auto-filled cells based on conditions. Its formulas calculate the weekday number's dates in each month. To use this table, you need to input the desired year, month, and the occurrence of the weekday in the month (e.g., the third Monday of the month or the second Friday, etc.). In this example, cell B2 shows that dates need to be found for the second occurrences of all days from Monday to Sunday. For instance, the second Monday of the third month or the second Friday of January.
The formula for determining the weekday occurrence date in a month:
=DATE($A3,$B3,1)+C$1-WEEKDAY(DATE($A3,$B3,1),1)+($B$1-(C$1>=WEEKDAY(DATE($A3,$B3,1),1)))*7
This formula performs certain basic mathematical operations to calculate the dates for the user-specified weekday occurrence in the month.
To use this table, fill in the two columns in the range A3:B14 with the years and month numbers of interest. Then, change the number in cell B2 to the desired occurrence of the weekday in the month. For example, if you’re interested in the date of the second Tuesday for each month, enter the value 2 and look at the dates in column D3:D14—these will show the dates for the second Tuesdays. If you need the dates for the third Thursdays, then enter 3 in cell B2 and look at the dates in column F3:F14—those will show all the third Thursdays for each month.
To better understand how the formula works, we transpose the table and present the same algorithm with different cell references in the function arguments.
=DATE($A3,$B3,1)+C3-WEEKDAY(DATE($A3,$B3,1),1)+($B$1-(C3>=WEEKDAY(DATE($A3,$B3,1),1)))*7
Now the table is vertical, and it may be easier to follow the formula logic in this format.
How to Use the Formula to Extract Dates for Weekday Occurrences
For example, this formula can be effectively used for quickly creating dynamic calendar templates in Excel. The formula will need to be slightly adjusted, but the basic algorithm principles remain the same:
We just need to create a table with the weekday numbers (1-7) as column headers and the occurrence numbers of the week (1-6) as row headers. Then, fill the 7x6 range of cells with just one formula, and your calendar is ready:
=IF(WEEKDAY(DATE($A$2,$C$1,1),1)>DAY(DATE($A$2,$C$1,D$2)),DAY(DATE($A$2,$C$1,1)+D$2-WEEKDAY(DATE($A$2,$C$1,1),1)+(($C4-1)-(D$2>=WEEKDAY(DATE($A$2,$C$1,1),1)))*7),DAY(DATE($A$2,$C$1,1)+D$2-WEEKDAY(DATE($A$2,$C$1,1),1)+($C4-(D$2>=WEEKDAY(DATE($A$2,$C$1,1),1)))*7))
In the formula arguments, you just need to specify the year and month or reference cells with these values, as shown in the example image above.
We will also need to use two conditional formatting rules for two ranges: the upper and lower. The upper range covers the first week of the month, and a simple formula is applied. Numbers smaller than seven days in this range should be highlighted in a different color:
=D4>7
This way, we indicate that these are days from the previous month, as typically shown in calendars.
The lower range for conditional formatting covers the last two possible weeks in the month and applies a more complex formula:
=D8<DAY(EOMONTH(DATE($A$2,$C$1,1),0))-14
However, a simpler formula with this logic can also be used: D8 < 23—any number less than 23 in this range should be highlighted in a different color because those are dates from the next month.
Dynamic Calendar Template in Excel with One Formula
As a result, we created a dynamic calendar in Excel. When you change the year in cell A2 or the month in cell C1, the calendar will automatically update and fill in the grid with the corresponding day numbers:
Download the Dynamic Calendar Template Using One Excel Formula 
You now have the ability to use this template as a calendar grid generator in Excel. Based on it, you can create your own custom calendar templates with graphic design and sell your masterpieces as digital products on Etsy.com and other marketplaces.
