November 2

0 comments

Excel – How can I create a list of Weekdays 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 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.

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.

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

Option #2: remove weekend dates using formulas  

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.

Excel show weekdays only

Option #3: remove weekend dates 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.

Excel show weekdays only

Option #4: 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. 

Excel show weekdays only

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.

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!

>