How to create a list of weekdays/business days in Excel?
This tutorial explains how to quickly create a list of weekdays or business days within a specified date range in Excel. Being able to generate such a list is especially useful for project scheduling, timesheets, attendance logs, or planning tasks that only take place during working days. The following methods show both manual and automated ways to accomplish this, catering to different scenarios and levels of Excel proficiency.
List weekdays with Auto Fill Options
List weekdays with a handy tool -- Kutools for Excel
List business days using Excel WORKDAY/WORKDAY.INTL formula
List weekdays/business days automatically with VBA code
List weekdays with Auto Fill Options
In Excel, you can rapidly create a list of weekdays using the Auto Fill handle combined with the Fill Weekdays option. This approach is straightforward and well-suited for quickly creating a sequential list of weekdays, but is less flexible if you require precise control over start and end dates or need to consider holidays.
1. Enter your starting date in an empty cell. For instance, type 7/1/2015 in cell A1. See screenshot:
2. Move your mouse to the lower-right corner of the cell until you see the fill handle (+). Then, click and drag the fill handle downward to as many rows as you require. For example, drag it to list 22 weekdays.
Tip: You'll need to estimate or calculate exactly how many weekdays you want to fill. If you're unsure, you can always fill more cells than needed and easily remove extras later.
3. Once you've finished dragging, click the small Auto Fill Options button that appears near the selection. In the popup menu, select Fill Weekdays. Excel will now only populate the range with weekdays (Monday through Friday), skipping weekends.
Now, you should see your sequence of 22 weekdays starting from 7/1/2015 displayed in the worksheet.
This method provides a quick way to fill a list of consecutive weekdays, but it is not ideal for custom business rules (such as excluding specific holidays or non-consecutive weekday intervals). Additionally, it does not provide a way to directly fill weekdays that fall between two exact dates. For more advanced needs, consider using one of the automated methods below.
List weekdays with a handy tool -- Kutools for Excel
Kutools for Excel provides a convenient solution to generate all weekdays or business days within a specific date range by using its Insert Random Data feature. This approach is flexible and time-saving, especially when dealing with large data sets or when you wish to ensure all returned dates are unique and filtered by weekday status. It is ideal for users who manage schedules or plan projects that depend on precise business-day calculations.
1. Select a blank range of cells where you want to generate the list. Make sure you select more cells than the anticipated weekdays, as the number of cells determines how many dates can be accommodated. See screenshot:
2. Go to the Kutools tab on the Excel ribbon, then click Insert > Insert Random Data. See screenshot:
3. In the Insert Random Data dialog box, switch to the Date tab. Enter your desired date range: type the earliest date in the From box, and the latest date in the To box. Check Workday to limit results to weekdays (Monday to Friday), and Unique Values to avoid any duplicate dates. See screenshot:
Note: If your project requires including holidays, you will need to manually remove them after generating the list, as the built-in function skips only typical weekends.
4. Click OK or Apply. The tool will populate your selected range with all matching weekdays from the specified period. Blank cells may remain if the selected range is larger than the possible result dates.
5. (Optional) To arrange the generated dates in chronological order, select the filled date range, go to the Data tab, then click Sort Oldest to Newest. This step ensures your list is well organized.
The generated list will now display all business days within your custom range, sorted in sequence from earliest to latest.
Kutools for Excel’s Insert Random Data utility supports more than just weekdays: you can also generate random numbers, random times, random unique values, random strings, and random custom lists. Click here to know more about this utility.
Note: Always double-check that the generated dates match your criteria, especially if you need to exclude specific holidays or if your date ranges cover variable work weeks.
Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy permanently free AI features! Download now!
List business days using Excel WORKDAY/WORKDAY.INTL formula
Excel’s WORKDAY and WORKDAY.INTL functions allow you to programmatically generate a list of sequential business days between two dates. WORKDAY skips weekends by default (Saturday and Sunday), while WORKDAY.INTL lets you specify custom weekend days and incorporate holiday dates. This approach is highly adaptable if you want to automate the process and ensure precision, such as for HR scheduling or payroll calculation.
For convenience, here is a step-by-step approach using formulas to build a dynamic business day list:
1. Enter your starting date in cell A1, for example: 7/1/2015. In cell B1, enter your ending date, such as 7/31/2015.
2. In cell C1, enter this formula to get the list of business days sequentially:
=IF(WORKDAY($A$1,ROW(A1)-1,$B$2)<=$B$1,WORKDAY($A$1,ROW(A1)-1,$B$2),"")Parameter explanation:
- $A$1: Start date (absolute reference)
- ROW(A1)-1: Increments the workday number for each row
- $B$2: Optional, a range containing holiday dates to exclude (enter your holiday dates in B2:B10 or any other desired range, or leave blank if not needed)
- $B$1: End date for the list (ensures the series stops at your selected endpoint)
3. Drag the formula in cell C1 downward until the cell displays empty. The list will dynamically populate all business days (excluding weekends or any holidays listed).
Tips:
- If you need to define custom weekends (e.g., only Sunday is a weekend), consider using the WORKDAY.INTL function, which allows specifying which days are weekends by providing a weekend argument (see Excel documentation for details).
- Always make sure your holiday dates are maintained in a clear, separate range to avoid accidental inclusion in your business day sequence.
- If holidays are not a concern, you can omit the optional holiday range in the formula.
Common error: If you get #NUM! or blank results where you expect dates, check that your start and end dates are valid and that your holiday list, if used, contains only dates (not text or other data).
List weekdays/business days automatically with VBA code
If you need to generate a complete list of weekdays or business days within any range, a custom VBA macro can automate the process. This method is ideal for advanced Excel users, or when you repeat this task frequently on different datasets. With VBA, you can account for custom workweek patterns, holidays, or even place the results anywhere you like in your worksheet.
1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications window. Then, click Insert > Module and paste the following code into the Module:
Sub ListBusinessDays()
Dim StartDate As Date
Dim EndDate As Date
Dim r As Integer
Dim ws As Worksheet
Dim currDate As Date
Dim Holidays As Range
On Error Resume Next
Set ws = ActiveSheet
StartDate = Application.InputBox("Enter start date:", "KutoolsforExcel", Type:=2)
EndDate = Application.InputBox("Enter end date:", "KutoolsforExcel", Type:=2)
Set Holidays = Application.InputBox("Select range for holidays (optional, press Cancel if none):", "KutoolsforExcel", Type:=8)
On Error GoTo 0
r = 1
For currDate = StartDate To EndDate
If Weekday(currDate, vbMonday) <= 5 Then ' Monday = 1, Friday = 5
If Holidays Is Nothing Then
ws.Cells(r, 3).Value = currDate
r = r + 1
Else
If Application.CountIf(Holidays, currDate) = 0 Then
ws.Cells(r, 3).Value = currDate
r = r + 1
End If
End If
End If
Next
End Sub2. Click the 
button or press F5 to run the code. You’ll be prompted to enter the start and end date for your list. Optionally, you can select a range of holiday dates to exclude (or simply press Cancel if not needed). The macro will then populate column C of the active worksheet with each business day, skipping weekends (Monday to Friday) and your specified holidays.
Notes & troubleshooting:
- If the dates aren’t showing, check that your input dates are valid and in date format.
- If a holiday range is not specified, all weekdays between the start and end dates are listed.
- The results always begin in column C. Adjust
ws.Cells(r,3)in the code if you want output elsewhere.
Best Office Productivity Tools
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in