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

0 comments

Posted: November 2, 2013

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.

How to calculate working days in Excel excluding weekends and holidays 1

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

How to calculate working days in Excel excluding weekends and holidays 2

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

How to calculate working days in Excel excluding weekends and holidays 3

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

How to calculate working days in Excel excluding weekends and holidays 4

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.

How to calculate working days in Excel excluding weekends and holidays 5

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.

How to calculate working days in Excel excluding weekends and holidays 6

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.

How to calculate working days in Excel excluding weekends and holidays 6

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. 

How to calculate working days in Excel excluding weekends and holidays 7

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.

Was this blog helpful? Let us know in the Comments below.

Excel Workday function

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

Want to learn how to create a list of weekdays or workdays only in Excel? Follow this step-by-step guide to learn four different options to create a list of weekdays only in Excel.

Instructions

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

  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 Fill Options box. Click the drop-down arrow on the options box and then select Fill Weekdays.

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.

  1. Type a date into a cell for example cell B4.
  2. In the cell below type equals, the WORKDAY function, bracket, The cell with the date in it, comma, 1 and a closed bracket. For example, in cell B5 type =WORKDAY(B4,1)
  3. Press Enter.

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

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.

For example, =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.

Option #4: Excel WEEKDAY and WORKDAY functions

You can use the IFWEEKDAY and WORKDAY functions to remove the weekends and every Thursday from your list.

  1. Enter a starting date in a cell, e.g. cell A5.
  2. To remove weekends and a workday use the following formula in the cell below the starting date. For this example, we are removing weekends and Thursday and the formula was typed into cell A6 with the starting date in A5.
  3. =IF(WEEKDAY(WORKDAY(A5,1))=5,A5+2,WORKDAY(A5,1)

Did you try this?

If you found this blog helpful please leave a comment below.

Elevate your Excel game and become a pro with our exclusive Insider Group.

Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus today!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Skip to Instructions
>