Create a series of workdays, excluding weekends and holidays

with No Comments

Create a series of workdays

www.excelguru.noIn this example I have written the function NextWorkDay that returns the next workday after the date you pass to the function. It makes it easy to create a series of dates that includes only workdays. Saturdays, Sundays and public holidays are excluded. And you can even exclude your own vacation if you want to.

(The article continues below the video pane)


The first date in the list is special

Create a series of workdays

For the first date in the list, cell A2, I use the formula


Where E1 is the year and E2 is the month

The part DATE(E1,E2,1) gives the first day of the month, so subtracting one from this date (DATE(E1,E2,1)-1) will return the first weekday of the month.

The rest of the formulas are really simple…

Create a series of workdays

The rest of the list is simple, just pointing to the cell above. For A3 this gives the formula



The exclusion list – holidays and vacation

By default Saturdays and Sundays are excluded from the list. In addition you can exclude public holidays and your vacation by entering the dates in the sheet Holidays. This worksheet must be present in the workbook to use the function NextWorkDay.



Download the workbook here:   SeriesOfDatesWorkdaysOnly.xlsm (617 downloads)