You will sometimes need to generate a sequence of working and/or non-working dates in Excel. In principle, the simplest way to accomplish this is to combine the SEQUENCE function with the FILTER function.
The SEQUENCE function generates a list of sequential numbers.
The SEQUENCE function syntax is as follows:
=SEQUENCE ( rows ; [columns] ; [start] ; [step] )
Rows specifies the number of rows to fill.
Columns specifies the number of columns to fill. If omitted, 1 column is returned.
Start specifies the starting number in the sequence. If omitted, it defaults to 1.
Step defines the increment for subsequent values. If omitted, it defaults to 1.
Rows and columns need to be positive whole numbers, while start and step can be any type of number.
We can generate a list of dates in a year by returning a sequence in which the number of rows will be the difference between the end of the year date and the start of the year date, with the start of that sequence being the start of the year date.
Remember, no matter how date and/or time are formatted and possibly edited, the underlying values saved in cells are still decimal numbers. Precisely because of that, in order to avoid complications, we will use the DATE function to define those start and end of the year dates:
Note, we will possibly have to format the values we’ve returned as dates:
Or to use a custom format:
The syntax of the FILTER function is as follows:
= FILTER ( array ; include ; [if_empty] )
The (return) array represents an array or a range of cells to return values from.
The include part of the argument typically consists of the corresponding array and filtering criteria defined for that corresponding array. Criteria are defined using the following Comparison operators:
equal to =
greater than >
greater than or equal to >=
less than <
less than or equal to <=
not equal to <>
The OR logic can be applied by using the addition operator + and/or the AND logic can be applied by using the multiplication operator *.
We can filter our weekends from our list of dates in a year by nesting our SEQUNCE function inside of a FILTER function, while defining our filtering criteria as being Monday to Friday.
Given that the WEEKDAY function will return an integer number representing the day of the week, by filtering in only the dates for which weekday is less than or equal to 5, we can generate a sequence of Monday to Friday dates:
Opposite, by filtering in only the dates for which weekday is greater than 5, we can generate a sequence of weekend dates:
With workdays, it gets messy.
In theory, it is simple because our only task is to filter our holidays. But what is a holiday?
States in the United States will have their own holidays, as will states in Germany, as will entities and nations in Bosnia and Herzegovina, as will cities in Switzerland. In Austria, there will be days when only banks are on holidays. But TARGET payments will operate on those days and on some other holidays, given that those are not TARGET holidays. And some TARGET holidays are not holidays in all of the eurozone, so banks will be open in parts of the eurozone, with TARGET payments not working on those days. In some countries, holidays falling on weekends are off on the closest workday, or some other workday, but not necessarily strictly defined which one. In others, they are not off at all. And both holidays and days off because of holidays falling on weekends can and often are changed by some law somewhere being updated or decree proclaimed.
Unfortunately, there is no such thing as a comprehensive and/or correct and/or regularly updated database and/or calculator of worldwide holidays, let alone a free one. The closest thing we have are actually Google Calendar and Microsoft Outlook (not readily or easily available in Excel!). The list of dates we will use in the following example is actually based on Microsoft Outlook, and it will follow its conventions, i.e., holidays falling on weekends appear twice, once as a holiday, and the second time marked with (Observed) to denote the days off because a holiday fell on a weekend.
Note, we will not calculate holidays or return them using a function. This is a manually edited list of days!
We can filter our holidays from our list of dates in a year by nesting our SEQUNCE function inside of a FILTER function, while defining our filtering criteria as being Monday to Friday and not being a holiday as declared on the holidays list (dates in column G). We will use a combination of MATCH, IFERROR and NOT functions in order to apply that filter:
To further improve our business days planning, we can additionally filter our collective vacation days from our list of collective vacation dates (yet another helper list) by nesting our SEQUNCE function inside of a FILTER function, while defining our filtering criteria as being Monday to Friday and not being a holiday as declared on the holidays list (dates in column G) and not being a vacation day as declared on our collective vacation days list (dates in column H):
For sequences of end-of-month, start-of-month, and the like dates, see this article.
Dig deeper: