How to Make an Interactive Calendar in Excel (2 Easy Ways)

The sample dataset below will be used for illustration.

Method 1 – Making Interactive Monthly Calendar in Excel

Step 1:

Open a new sheet and create four fields for user inputs and name them.
To create the monthly calendar for the year 2023, input that in the year field.

Step 2:

Select cell C5 and go to the Data tab of the ribbon.
From the Data Tools group, select Data Validation.

Step 3:

Under the Allow dropdown select List and in the Source type box, select the data range of the twelve months.
Press OK.

This will create a dropdown of the twelve months in cell C5.

Step 4:

Create a 7×7 table.
Enter the day names of the week in the created table. Monday being in the first column.

Step 5:

To make the header a dynamic one, join the cell values of C5 and C4 by entering the following formula.

=C5&" "&C4

Step 6:

The header will change to the values from cell C5 and C4.

Step 7:

Enter the the MATCH function in cell G4.

=MATCH(C5,'Data Set'!$B$17:$B$28,0)

The MATCH function will search the cell value of C5 in the B17:B28 cell range and will return the relative position of that month in that range.

Step 8:

Press Enter to find the month number of cell C5 in cell G4.

Step 9:

Determine the first day for each month in a year in the form of a number.
1 will represent Monday, 2 will represent Tuesday etc.
Insert the following combination formula of WEEKDAY and DATE functions in cell G5.

=WEEKDAY(DATE($C$4,$G$4,1),2)

The Date function will show the first date of each month of a specific year.
The combination formula will return the first day for each month through the WEEKDAY function.
Start the week from Monday. Enter 2 as the return type in the formula.

Step 10:

The formula will show the number corresponding to the first day of January 2023 which is 7 and a Sunday.

Step 11:

To fill up the table with numbers, enter the following formula of the SEQUENCE function in cell B9.

=SEQUENCE(6,7)

Step 12:

There are 6 columns and 7 rows in the table. The table will fill up by numbers from 1-42 serially.

Step 13:

To convert the numbers into dates, enter the following formula in cell B9.

=DATE(C4,G4,1)+SEQUENCE(6,7)

Step 14:

Press Enter to get the result as shown in the image below.

Step 15:

The outputs are not in the correct format.
To modify them, select the cell range B9: H14 and choose the Number Format dropdown in the Number group.

Step 16:

Select Short Date as the format for the selected cell range.

Step 17:

The starting date or day of Jan 2023 is not correct.

Step 18:

To modify it, enter the following formula in cell B9:

=DATE(C4,G4,1)+SEQUENCE(6,7)-G5

Step 19:

The result shows the starting date for January as Sunday, as shown in the image below.
There are some extra dates from the previous and following months of January 2023.

Step 20:

Extract those extra days from the calendar by entering the following combination formula in cell B9.

=IF(MONTH(DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5,"")

Formula Breakdown

=IF(MONTH(DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5,””)

DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5: This part of the formula expresses that the dates in the calendar are organized serially with the consideration of dates and weekdays with along with values from cell C4, G4 and G5.
IF(MONTH(DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5,””): This part shows the whole conditions along with months, dates and weekdays.

Step 21:

The monthly calendar dates are updated correctly as shown in the image below.

Step 22:

Right-click on the mouse after selecting the data range B9:H14 to transform the format of dates.
From the context, select Format Cells.

Step 23:

The Format Cells dialog box will open.
Go to the Custom tab in the dialog box.
Under the Type label, type dd.
Press OK.

Step 24:

This keeps the dates in the table.

Step 25:

Change the font color for the weekends from black to red.

Step 26:

When you change the month name from the dropdown in cell C5, the dates will also be changed in the data range of the monthly calendar.

Step 27:

Select the cell range B5:H14 to highlight the holidays for each month.
Go to the Home tab of the ribbon and select Conditional Formatting.
From the dropdown, choose New Rule.

Step 28:

The New Formatting Rule dialog box open.
Under Select a Rule Type header, select Use a formula to determine which cells to format.
Enter the following combination formula of ISNUMBER and VLOOKUP.

=ISNUMBER(VLOOKUP(B9,'Data Set'!$C:$C,1,0))

Press Format to format the cells.

Formula Breakdown

=ISNUMBER(VLOOKUP(B9,’Data Set’!$C:$C,1,0))

VLOOKUP(B9,’Data Set’!$C:$C1,0): The VLOOKUP function looks for a value in the leftmost column of the holiday table from the Data Set worksheet, and returns the value in the same row from the column you specify.
ISNUMBER(VLOOKUP(B9,’Data Set’!$C:$C,1,0)): The ISNUMBER function will check if the value from the previous step is a number and will return TRUE or FALSE as the output.

Step 29: