How to Create Calendar Without Weekends or Holidays in Excel

Often, when creating reports in Excel, it's necessary to use a workday chart excluding weekends and holidays. Such a table is useful for calculating daily turnover, cash flow on weekdays, and more.



How to Create a Workday Chart in Excel?

One of the simplest methods to create a workday chart without weekends and holidays is based on using the WORKDAY.INTL function.

To start building the chart, specify the date of the last working day of the previous year. Next to it, create a list of only holiday dates (excluding weekends) that are non-working days for your company for the current year. All values should be in the "Date" cell format. An example of starting the workday chart creation is shown below:

In cell A3, enter the first formula referencing cell B1 with the date of the last working day of the previous year:

=WORKDAY.INTL(B1,1,1,$B$3:$B$14)

Then, in cell A4, input the second formula for subsequent copying into other cells:

=WORKDAY.INTL(A1,1,1,$B$3:$B$14)

Now copy this formula into the remaining cells of this column as many times as needed to obtain the workdays for the current year's chart:

As a result, a personalized production calendar for 2025 is created for a specific company.

The WORKDAY.INTL function returns a workday date based on the number of days by which the specified date should be increased. The function has 2 mandatory and 2 optional arguments:

=WORKDAY.INTL(Start_Date; Days; Weekends; Holidays)

1. Start_Date: the date from which to start calculating.
2. Days: the number of days to calculate from the start date.
3. Weekends: By default, if this optional argument is not filled, the function considers Saturdays and Sundays as weekends. However, this argument allows you to specify which days of the week to consider as weekends. During manual entry in the formula bar or an Excel cell, an interactive hint appears, allowing you to choose from 15 available weekend variations or simply specify the variation code, i.e., a number from 1 to 15. Changing the workday schedule in Excel:

4. Holidays: an optional argument where you specify a list of holiday dates so that they are not used in calculations and are excluded from the final result.

How is the Workday Chart Structured in Excel?

In the formula of this example, Excel starts calculations from the date 31.12.2024. During copying, it increases by 1 day, determining and returning the next workday. The optional arguments indicate that Saturdays and Sundays are weekends, just like the holiday dates entered in a separate range of cells.

It's important to note that the reference to the holiday date range address should be absolute. This ensures that the link address remains unchanged when copying the formula to other subsequent cells.

Applying the WORKDAY.INTL Formula in Practice

In the Excel application, a Gantt Chart for 4 managerial tasks to be completed in January is created. It's worth noting that managers shouldn't work on weekends and holidays. Therefore, the Gantt Chart should be refined to automatically exclude weekends and holidays from the user-specified visualization conditions. The table of holiday dates is the criteria for the formula:

To implement the given task in cells with date labels, a complex formula will be used:

Now the user can manage the date scale by including/excluding holidays and changing the number of weekends from 0 to 2:

Download

As seen, this template can adapt to various corporate management cultures: for weeks with five working days and two weekends and for weeks with six working days and one weekend. Additionally, holiday dates can be excluded based on conditions and edited in a separate source data table.