You can make quick lists with Excel’s AutoFill feature. In the short video below, I show two tricks for making weekday lists – only the Monday to Friday dates, with no weekend dates included.
Video: Make Weekday Lists in Excel
In this quick video, I show two easy ways to make an Excel weekday list, with no weekend dates.
Both of these tricks use AutoFill so they’re quick and easy to use.
There are written steps below the video, and the full transcript is further down the page.
Trick 1 — AutoFill Weekdays (skip weekends)
Use this technique when you want a list of workdays, from Monday to Friday, and then skip the Saturday and Sunday dates.
- First, type your starting date in a worksheet cell.
- Select the start date cell (cell B3 in the screen shot below)
- Next, point to the Fill Handle, at the bottom-right of the start date cell.
- Tip: The pointer changes to a 4-headed arrow when it’s over the Fill Handle
- Press and hold the right mouse button
- Drag down the column, to the cell where you want the weekday list to end
- Let go of the right mouse button
- In the pop-up menu that appears, click on the Weekdays option
List of Weekdays
On the worksheet, Excel fills the cells with dates from Monday to Friday.
Then it skips the weekend dates, and continues with the next week’s weekdays.
Weekday Names in Column A
In the video, and the screen shot above, the weekday names appear in column A, after the dates are listed in column B.
Those names make it easier to confirm that the date list is showing only weekdays.
To show those day names, here’s the formula that I entered in cell A3, and copied down to cell A12.
- =IF(B3=””,””,TEXT(B3,”ddd”))
How The Formula Works
First the formula uses the TEXT function, to format the date as a 3-character day name.
- TEXT(B3,”ddd”)
Next the IF function checks cell B3.
- If the cell is empty , then the formula result is an empty string
- IF(B3=””,””,
- If the cell is NOT empty, then the formula result is the day name, returned by the TEXT function
Trick 2 — Every Second Weekday
You can get even fancier with Excel AutoFill, when you use the Series settings.
Here are the steps to make a list with every second weekday date, instead of all the weekdays.
- First, type your starting date in a worksheet cell.
- Select the start date cell (cell B3 in the screen shot below)
- Next, point to the Fill Handle, at the bottom-right of the start date cell.
- Press and hold the right mouse button
- Drag down the column, to the cell where you want the date list to end
- Let go of the right mouse button
- In the pop-up menu that appears, click on the Series option
- Tip: Series is at the bottom of the pop-up menu
Series Dialog Box
When the Series dialog box opens, make the following 2 changes to the settings:
- For Date Unit, choose Weekday
- Change the Step value to 2
Leave the other settings unchanged, then click the OK button
List – Every 2nd Weekday
On the worksheet, Excel fills the cells with dates for every second weekday.
- In the screen shot below, Monday, Wednesday and Friday are listed for the first week
- The next week has Tuesday and Thursday dates
The rest of the list repeats those weekly patterns, to show every second weekday’s date.
More AutoFill Tips
- You can find more Excel AutoFill tips and examples on my Contextures site.
- There’s an AutoFill examples file that you can download on my site too!
- The Microsoft website also has details on creating a series of numbers, dates, or other items.
Video Transcript
Here’s the full video transcript for the Fill Weekdays video.
- Note: I’ve added a couple of headings, and some formatting, to make the transcript easier to read..
———-START OF TRANSCRIPT———
Weekday List
Here are a couple of quick tricks for creating a list of weekday dates.
- I’ve got a starting date and I’m going to fill down.
- I’ll start by pointing to the fill handle at the bottom right corner here and I’m pressing the right mouse button while I drag down.
- And when I let go, this menu pops up and I can see weekdays.
A formula here shows the code for the weekday name.
- So, we go Monday to Friday.
- Then, it skips Saturday and Sunday and continues the next week.
Every Second Weekday
On this sheet, I just want every second weekday.
- I’ll start again here.
- Point to the fill handle, press the right mouse button, and drag down.
- This time, I’m going to choose Series.
- In here, we’re going to choose Weekday.
- And instead of a step value of 1, I’m entering a 2.
- Click OK.
And now it’s Monday, Wednesday, Friday, and then Tuesday and Thursday the next week, and so on down the list.
———-END OF TRANSCRIPT———
_______________________