Autofill Only Weekday Dates in Excel: 2 Ways
To autofill only weekday dates in Excel, use the fill handle and then choose “Fill Weekdays” from the Auto Fill Options menu, or use a formula like =WORKDAY(A1,1) to generate a sequence that skips Saturdays and Sundays. Both methods give you a series of dates that excludes weekends, which is useful for business schedules, project timelines, and work logs.
Why You Need Weekday-Only Autofill
Excel’s standard date autofill increments by one day, which means a simple drag will include Saturdays and Sundays. For most work-related spreadsheets—shift rosters, delivery schedules, payment due dates, or any business day tracker—those weekend dates are noise. The weekday-only fill keeps your list clean and accurate without manual deletion or conditional formatting hacks.
The feature has been in Excel for decades and works the same way in Excel 2016, 2019, 2021, and Microsoft 365. It also works in Google Sheets, though the exact menu wording differs slightly. If you have ever dragged a date down and noticed a small square with a plus sign appear at the bottom-right of the selection, you already have access to the tool.
Method 1: The Fill Handle with Auto Fill Options
This is the quickest method and requires no formulas. It works when you have at least one starting date and you want to extend the sequence downward or across.
- Type your starting date in a cell, for example
3/1/2025in cell A1. - Select the cell. Move your mouse to the bottom-right corner until the cursor changes to a thin black cross (the fill handle).
- Click and drag down (or across) for as many cells as you need. Excel will initially fill every day.
- Release the mouse button. A small icon appears at the bottom-right of the filled range—this is the Auto Fill Options button.
- Click that icon and select Fill Weekdays.
Excel instantly recalculates the range, removing all weekend dates. If your original drag covered 10 cells, after choosing “Fill Weekdays” you will see only the weekdays from your start date onward, and the list will be shorter than 10 items because weekends were skipped. If you want exactly 10 weekdays, you need to drag far enough to cover roughly 14 calendar days.
Method 2: The WORKDAY Formula
When you need more control—say, a sequence that also skips holidays, or a list that updates dynamically when you change the start date—use the WORKDAY function. This is the same function Excel uses internally for the fill handle trick, but exposing it in a formula lets you customize it.
In cell A1, enter your start date. In cell A2, enter:
=WORKDAY(A1,1)
Then copy that formula down as far as needed. Each row adds exactly one workday, skipping weekends. If you want to skip specific holidays, add a range of holiday dates as the third argument:
=WORKDAY(A1,1,$C$1:$C$5)
where C1:C5 contains the holiday dates. This is especially useful for project plans where you know the company holidays in advance.
What Counts as a “Weekday”?
By default, Excel considers Monday through Friday as weekdays. That matches the standard business week in most countries, but not all. If your work week is Sunday through Thursday, or Saturday through Wednesday, the default weekday fill will not give you the correct sequence.
For non-standard weeks, you need the WORKDAY.INTL function. It allows you to specify which days are weekends using a code. For example, to treat Sunday and Monday as the weekend, use:
=WORKDAY.INTL(A1,1,11)
The second argument is a number that represents which days are off. The codes run from 1 to 17, and the most common ones are:
- 1 – Saturday and Sunday (default)
- 2 – Sunday and Monday
- 3 – Monday and Tuesday
- 7 – Friday and Saturday
- 11 – Sunday only
You can also use a seven-character string of 1s and 0s, where 1 means weekend and 0 means workday. For example, "0000011" means Saturday and Sunday are off, which is the same as code 1.
Common Problems and Fixes
Several issues can trip up users when trying weekday autofill. Here are the most frequent ones and how to resolve them.
- The Auto Fill Options button does not appear. This happens if you drag without releasing the mouse properly, or if you are using a touch screen. Try dragging again and make sure you release the button while the cursor is still over the fill range. On touch devices, tap the small menu that appears after dragging.
- The fill handle is disabled. Go to File > Options > Advanced and check the box “Enable fill handle and cell drag-and-drop.” If it is unchecked, the handle will not work.
- Dates are filling as numbers. This usually means the cells are formatted as numbers, not dates. Select the column, press Ctrl+1, and choose a date format under the Number tab.
- The sequence skips holidays you wanted to include. The default weekday fill only skips weekends, not holidays. To skip holidays, use the WORKDAY formula with a holiday range.
- Dragging the fill handle copies the same date instead of incrementing. Hold down the Ctrl key while dragging to force Excel to increment the date. Without Ctrl, Excel may copy the value if it detects a pattern it doesn’t understand.
Weekday Fill vs. Sequence Function
Excel’s SEQUENCE function (available in Excel 365 and 2021) can also generate date lists, but it does not natively skip weekends. You would need to combine it with WORKDAY or filter the results. For example, to generate the first 10 weekdays starting from a date in A1:
=WORKDAY(A1, SEQUENCE(10)-1)
This returns a dynamic array of 10 dates, all weekdays. The advantage is that you do not need to drag the formula down—the array spills automatically. The disadvantage is that it is less intuitive for beginners, and it requires a recent Excel version.
For most users, the fill handle method is simpler and works everywhere. The SEQUENCE approach is worth learning if you frequently need dynamic, formula-driven lists that update when you change the start date.
Frequently Asked Questions
Does weekday autofill work in Google Sheets?
Yes. In Google Sheets, type a date, drag the fill handle, and look for the Auto Fill Options icon. It offers “Fill Weekdays” just like Excel. The WORKDAY function also exists in Google Sheets with the same syntax.
How do I autofill weekdays excluding holidays?
Use the WORKDAY formula with a holiday range. In Excel, enter =WORKDAY(A1,1,$C$1:$C$5) and copy down. In Google Sheets, the function is the same. The holiday range must be a list of actual date values, not text.
Why does my weekday fill include Monday but skip Friday?
That likely happens because your start date is not a weekday, or because the fill handle is set to “Fill Days” instead of “Fill Weekdays.” Check the Auto Fill Options menu after dragging and explicitly choose “Fill Weekdays.” If you start on a Saturday, the first filled date will be the following Monday.
Can I autofill weekdays in a row instead of a column?
Yes. Drag the fill handle horizontally to the right instead of downward. The Auto Fill Options menu appears the same way, and “Fill Weekdays” works in both directions.
Is there a way to autofill weekdays without using the mouse?
Yes. Use the keyboard: type your start date, then press Ctrl+Shift+Down Arrow to select the range you want to fill, then press Alt+H-F-I-S and choose “Weekdays” from the dialog. The exact keystrokes vary by Excel version, but the menu path is Home > Fill > Series > Weekday.
What if I need to skip only certain weekdays, like every Wednesday?
The standard weekday fill cannot do that. Use a formula that checks the weekday number and only increments when the day is allowed. For example, to skip Wednesdays, use =A1+IF(WEEKDAY(A1+1)=4,2,1) and copy down. This adds 2 days when the next day is Wednesday, otherwise adds 1.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.