Make a Calendar using the SEQUENCE Function

It is elementary to design a calendar in excel in just three steps. We can make the calendar using the SEQUENCE Function. We would use formulas containing functions like EOMONTH, CHOOSE, SEQUENCE, DATE, WEEKDAY, and conditional formatting.

It is assumed you understand the basic functioning of these topics. If you do not have prior knowledge of any of these functions, then it is highly recommended to learn their basic usage as making the calender would involve it.

Calendar Using Sequence Function

The Calendar we are about to make would input the month and year from the user and then show the corresponding calendar for that month of the specified year.

making a calendar in excel using sequence function

We can change the value in input cells for month and year and this calendar is going to automatically update itself.

Since we already mentioned that making this calendar involves three steps. These steps are as follows.

  • preparing input cells
  • writing formulas
  • formatting

You can have the practice workbook to work along this article.

Step 1 – Preparing Input Cells

We need to have two input cells, one is for specifying month and another is for specified year. We can apply different cell styles to make them look different.

cell styles in excel

Select the Red cell style for inputs.

input for making calendar
infographics making a calendar in excel

Step 2 – Applying Formulas

We would now use the following formula in cell B7.

=DATE(C5,C4,1)

This would return the first day of the month and year specified in the input cells.

making a calendar in excel using sequence function step 2

Now use the following formula in cell B9.

=SEQUENCE(6,7,CHOOSE(WEEKDAY(B7),1,0,-1,-2,-3,-4,-5))

Note that we have written the labels for Weekday in the 8th row as S, M, T, W, T, F, S

making a calendar in excel using sequence function step 3

Explanation – The result of the sequence function is spilled in the range B9:H14. We have used the SEQUENCE Function to get the sequence of numbers. The rows and columns argument is hard coded as 6 rows and 7 columns ( for all weekdays ). The start argument is optional in the SEQUENCE Function and we have made the starting number in cell B9 dynamic using this formula.

CHOOSE(WEEKDAY(B7),1,0,-1,-2,-3,-4,-5)

This starting number would define on which day the 1st of each month in the calendar would lie. If the weekday of the first of that month is Monday, then 1 must come in cell C9 and the starting number is 0.

Weekday on the 1st of the month1st of the month lies in the cellValue in cell B8
SundayB91
MondayC90
TuesdayD9-1
WednesdayE9-2
ThursdayF9-3
FridayG9-4
SaturdayH9-5

Step 3 – Applying Formatting

We would now apply two conditional formatting rules in order to hide the negative value of days and the invalid days.

applying conditional formatting in excel

Hiding these irrelevant dates is mandatory in a calendar. To do so, we would use two conditional formatting rules.

  • Select the range B9:H14.
applying conditional formatting step 2
  • Click on the Conditional Formatting button and go to the Less Than formatting rule.
  • Enter the value as 1 and choose the Custom formatting rule.
    applying conditional formatting step 3
    • Set the type field number format to ;;; ( three semicolons ) and click ok
    applying conditional formatting step 4qq

    This would hide values less than 1 on the calendar.

    • Add a new rule for the cell values greater than.
    applying conditional formatting step 5
    • Enter the following formula and then set the custom format to three semicolons to hide the invalid dates.
    =DAY(EOMONTH($B$7,0))
    applying conditional formatting step 6

    This would make the calendar start working logically.

    We will now change the formatting.

    • Select the range B7:H7 and press the ctrl 1 keys.
    • Go to the Alignment tab and set the horizontal alignment to Center Across Selection.
    formatting the calendar
    • Set the custom number format to dd mmmm
    formatting the calendar step 2

    Thereafter, you can format the weekdays and headings as per your choice.

    This brings us to the end.

    Leave a Comment