If you want to generate a series of only working days, which is Monday to Friday in a month falling after a particular date, then you can do this task very easily in Excel by using a simple formula which uses the WORKDAY function.
Generate a series of dates by workdays in Excel
The WORKDAY function
The function displays working days falling after a specific date. Its syntax is:
Arguments in the WORKDAY function
- Start_date – The beginning date
- Days – The working days after or before the beginning date.
- Holidays – The non-working dates.
If you want to see only a series of workdays, we’ll use the formula as shown:
Excel uses the WORKDAY function with the above-given formula and returns the next working day.
Let us understand it with the help of an example.
Example of the WORKDAY function
Figure 1: Illustration of the WORKDAY function
In the example given above, we provide a hard code date in B6. This is the starting date and to get the working days, we use the formula in the cell B7 as shown:
This formula returns next working day, i.e., 15/10/18 as the result since 13th and 14th are Saturday and Sunday.
If you need any other holiday to be considered, then you can add the optional argument of the holiday along with the formula in the WORKDAY function:
The WORKDAY.INTL function
We can also use WORKDAY.INTL function for the same. When we are working with the custom weekends which are actually the working days, then we need to use the WORKDAY.INTL function. This will allow you to set the days to weekends by putting the weekend argument in a numeric code.
The syntax for the WORKDAY.INTL function is:
start_date = the start date of the week
days = the end of the week
weekend = optional days that are considered weekends in the workweek
holidays = optional days that are considered non-work days
By default, this function will exclude Saturdays and Sundays as weekends unless you specify otherwise. You can do this using a series of zeros and ones in the weekend section, going from Monday through Sunday, with zeros indicating workdays and ones indicating weekend days.
This can also be accomplished with a set of codes as follows:
Code = Weekend Days
1 = Saturday, Sunday
2 = Sunday, Monday
3 = Monday, Tuesday
4= Tuesday, Wednesday
5 = Wednesday, Thursday
6 = Thursday, Friday
7 = Friday, Saturday
11 = Sunday only
12 = Monday only
13 = Tuesday only
14 = Wednesday only
15 = Thursday only
16 = Friday only
17 = Saturday only
In the following example, you can see the start date of Friday 1/4/2019. The WORKDAY.INTL formula determines the end workday for a five-day period in three different scenarios.
In Figure 2, there are no weekends included. The syntax for the formula is:
In Figure 3, the weekends are the default Sat & Sun, so the code “1” is used in the formula as follows:
In Figure 4, the weekend is Saturday only, so the code “17” is included as follows:
These all give a different result as the last workday in the series.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.