November 2

0 comments

Excel – How to create a list of Working Days only

By Sharyn Baines

November 2, 2013

Dates, Entering Data, Functions, Popular

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.

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.

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!

>