How to create a list of Working Days – Excel WORKDAY function

This post has been updated and added to by Sharyn, November 2020.

Need a list of weekdays or workdays only?

Let’s look at four different options you that can use to create a list of weekdays only in Excel.

To calculate the number of working days between two given dates in Excel, have a look at our blog NETWORKDAYS in Excel.

Option #1: create a fixed list of weekdays only (no formulas required)  

The Auto Fill button allows us to quickly create a lists of days of the week.

However, the list includes Saturday and Sunday.  Thankfully Excel offers a very simple solution to create a list that only includes weekdays.

1. First enter your starting date into a cell.

2. Now click and drag the Auto Fill button to display dates going forward.

3. When you release your mouse button Excel will display the Auto FillOptions box. Click the drop-down arrow on the options box and then select Fill Weekdays.

Excel will remove any dates that fall in a weekend and only dates for weekdays will be displayed.

Option #2: Excel WORKDAY function (week days only)

You can easily remove weekend dates and create a list of weekdays only using the WORKDAY function.

In my example below, cell B4 is the starting date. This date has just been typed into cell B4.

In cell B5 the formula =WORKDAY(B4,1) has been entered. This allows you to take a start date and increase it by a set number of days. In this example we are increasing the date by 1 day. But the best thing about the WORKDAY function is it removes Saturdays and Sundays. Therefore you can copy this formula down to create a list of weekdays only. If you would like to customise your weekend dates, please use the WORKDAY.INTL function.

Option #3: Excel WORKDAY function (remove weekends and holidays)

The WORKDAY function allows you to remove holidays too.

In my example below, =WORKDAY(B4,1,$B$14:$B$16) is increasing the date by one day, removing any weekend dates AND it is removing any dates listed in B14 to B16. Therefore, if you would like to remove any specific dates from your list, create a list of these dates within your workbook and include this range as the last part of your WORKDAY function.

Option #4: Excel WEEKDAY and WORKDAY functions  

Remove weekend dates and specific weekdays using formulas.

Using the IF, WEEKDAY and WORKDAY functions we will remove the weekends and every Thursday from the list.

First, I’ll introduce the WEEKDAY function.

In the example below, cell B2 is using the WEEKDAY function to identify the day of the week. Using WEEKDAY you can identify days using numbers. By default, Sunday = 1, Monday = 2… and so on, up to Saturday = 7. In the example =WEEKDAY(A2) is identified as a 3, being a Tuesday, Thursday is identified as a 5.

In the example below, cell A5 is the starting date. This date has just been typed into cell A5.

In cell A6 the IF, WEEKDAY and WORKDAY functions are used to remove weekends and every Thursday. 

Using the formula =IF(WEEKDAY(WORKDAY(A5,1))=5,A5+2,WORKDAY(A5,1)) we can ask Excel to check if the returned date using the WORKDAY function WORKDAY(A5,1) is a 5 (a Thursday). If it is, add two days to the date, therefore jumping to a Friday. If it isn’t just add 1 day. The WORKDAY function will remove all weekend dates and the IF and WEEKDAY functions will ensure all Thursdays are removed also.

The formula can then be copied down the column and Excel will only the show dates required.

Watch the Excel create a list of business days only tutorial

[Watch on YouTube] / [Subscribe to our YouTube Channel]

Was this post helpful? Please let me know in the comments below.