How to Generate a Series of Dates by Workdays in Excel

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:

=WORKDAY(start_day,days,[holidays])

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:

=WORKDAY(date,1)

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:

=WORKDAY(B6,1)

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:

=WORKDAY(date,1,holidays)

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:

=WORKDAY.INTL(start_date,days,[weekend],[holidays])

Where:

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. 

Figure 2.

In Figure 2, there are no weekends included. The syntax for the formula is:

=WORKDAY.INTL(B4,C4,”0000000″)

Figure 3.

In Figure 3, the weekends are the default Sat & Sun, so the code “1” is used in the formula as follows:

=WORKDAY.INTL(B4,C4,1)

Figure 4.

In Figure 4, the weekend is Saturday only, so the code “17” is included as follows:

=WORKDAY.INTL(B4,C4,17)

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. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar