Skip to main content

How to create a list of weekdays/business days in Excel?

This tutorial is talking about how to create a list of weekdays between a date range in Excel, if you are interested in this, just go down to read the details.

List weekdays with Auto Fill Options

List weekdays with a handy tool -- Kutools for Excel

arrow blue right bubble List weekdays with Auto Fill Options

In Excel, you can list weekdays with the Autofill handle and choose the Fill Weekdays option.

1. Type the start date you need in a blank cell. See screenshot:

doc list weekdays 1

2. Then drag the right corner of the date cell down to auto fill the dates. See screenshot:

Tip: You need to know how many weekdays (cells) you need. Here I need to list probably 22 weekdays.

doc list weekdays 2

3. Then you can click the Auto Fill Options to show the context menu, and check Fill Weekdays option. See screenshot:

doc list weekdays 3

Now you can see all 22 weekdays start from 7/1/2015 are list.

doc list weekdays 4

But this method is not convenient to quickly list business days between two dates, to save much working time, you can refer to next method.

arrow blue right bubble List weekdays with a handy tool -- Kutools for Excel

If you have Kutools for Excel installed, you can use the Insert Random Data to insert all weekdays or weekends between a dates range, and then sort the date as you need.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

After free installing Kutools for Excel, please do as below:

1. Select a cells range (you’d better select much more cells than the dates you need to list). See screenshot:

doc list weekdays 5

2. Click Kutools > Insert > Insert Random Data. See screenshot:

doc list weekdays 6

3. In the Insert Random Data dialog, click Date tab, then type the start date in the From box, type the end date in the To box, and check Workday option and Unique Values option. See screenshot:
doc kutools insert random data 2

4. Click Ok or Apply, now all the weekdays of July have been inserted. See screenshot:

Tip: If the cells you selected are more than the inserted dates, there will be some blank cells left.

doc list weekdays 8

5. Go to Data > Sort Oldest to Newest to sort the dates from old to new. See screenshot:

doc list weekdays 9

Now the weekdays are listing from oldest to newest.

doc list weekdays 10

With Insert Random Data, you can quickly insert random integers, random time, random date, random unique values, random string and random custom list. Click here to know more about this utility.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Try this =IFERROR(IF(MONTH($Start Date$)<>MONTH(A1+1),"",(IF(WEEKDAY(A2)=6,A2+3,A2+1))),"") This formula will give you working days in a list without ant error
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations