Create a series of workdays
In 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
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…
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 (452 downloads)