How to Set Excel to Autofill Weekday Only Dates (Formula):

You might need to only include weekday-occurring dates in a column when working with dates in Excel.

This is typically required when drafting a project plan because weekends are usually considered holidays, and only weekday dates can be used.

In this brief lesson, I’ll demonstrate two easy ways to use Excel to automatically fill in weekday dates for a column.

We’ll first look at an Excel tool that can rapidly calculate this amount, and we’ll also discover a formula you can use to look at an Excel tool that can rapidly calculate this amount. We’ll also find a recipe you can use to only retrieve weekday dates.

Let’s start now!

This instruction includes:

How to Use the Autofill ‘Fill Weekday’ Option
1.1 How to Fix If the Fill Handle Isn’t Visible?
Finding Weekday Dates Only by Formula

How to Use the Autofill ‘Fill Weekday’ Option:

A built-in auto-fill feature in Microsoft Excel called “Create Weekdays” enables you to swiftly fill a list of weekday-only dates.

Let me demonstrate how it operates.

I want to populate the cells below cell A1 with just weekday dates. Below is a date in cell A1 (i.e. Monday to Friday).

The steps are as follows:

Choose cell A1
The selection’s bottom right corner should have the mouse pointer there. The cursor will now be a + sign, as you can see. The fill handle is what this is:
Hold the left mouse button to automatically fill the column’s cells while dragging the fill handle downward.
In the bottom right corner of the chosen cells, click the Auto Fill option icon.
Select “Fill Weekdays” from the box.

The aforementioned actions would only take place during the weekdays and instantly populate the column with a list of dates.

If the Auto Fill indicator is not visible, you may still do the same task by choosing the cells, selecting the Home tab, selecting the Editing group, selecting the Fill option, and then selecting the Series option. By doing so, the Series dialogue box will pop up, allowing you to fill the full column with only weekday dates by choosing the Weekdays option.

Even though this is a quick and simple technique to rapidly fill a column with simply the weekday dates, keep in mind that it will count Saturday and Sunday as weekends and Monday through Friday as weekday days.

It doesn’t let you pick which days you wish to take into account as weekdays.

For instance, this approach cannot be used if you want the weekends to include Sunday and the weekdays to have dates that fall between Monday and Saturday.

In this instance, you would be better off utilizing the formula technique discussed later in this article.

1.1 How to Fix If the Fill Handle Isn’t Visible?

Some individuals complained that they could not see the fill handle when they chose a cell when I published this tip on my YouTube channel (i.e., when they place the cursor at the bottom right of the cell selection, their cursor does not change into a plus icon).

If you’re having the same issue, follow these steps to resolve it:

Select File from the ribbon by clicking it.
Select Options. This will bring up the dialogue window for choices.
In the Excel Options dialogue box’s left pane, choose the Advanced option.
Check the box next to “Enable fill handle and cell drag and drop.”
Press “OK”
The fill handle ought to be usable in the spreadsheet at this point.

2. Finding Weekday Dates Only by Formula

The WORKDAY.INTL method makes it simple to populate a column with dates that fall on particular days.

WORKDAY.

The INTL function lets you select which days of the week are acceptable in addition to returning the date after a given number of days.

Let me first demonstrate how it functions before going into great depth about the formula.

Column A1 in the table below has a date, and I want to only include weekday dates in the column’s contents (where we give it would be Monday to Friday).

Here is a formula We can use to do this:

=WORKDAY.INTL(A2,1,"0000011")

I can replicate this formula for all the other cells in the column once I have it in cell A2 in the column.

How does this equation function?

The WORKDAY.INTL function accepts three arguments:
The start date, which I have in cell A1, is the date.
days after which I wish the date is indicated. In this instance, I have maintained this as one day since I require a series of days to follow one another.

The weekend is when I may decide which days should be regarded as weekdays and which should be designated weekends.

The third parameter, where I used “0000011,” is where the magic happens.

Each of the seven numerals in the double quotes indicates whether the day should be regarded as a weekday or a weekend. If the number is 0, it would be a weekday, and if it were 1, it would be the weekend.

In the case of the number 0000011, the first five days of the week—Monday through Friday—would be regarded as weekdays, while Saturday and Sunday, the latter two days of the week, would be regarded as weekend days.

Therefore, it ensures that only working days—and not weekends—are returned when the WORKDAY.INTL function returns the next date.

This formula’s biggest feature is that any day can be designated as a working or non-working day.

To only display dates that fall on Monday, Wednesday, and Friday in a column, you may change the third parameter in the formula as follows:

=WORKDAY.INTL(A2,1,"0101011")

The method above can be changed to just return dates for the weekends. To do this, you must modify the third parameter such that the first five days are considered non-working days and the final two days are considered working days.

=WORKDAY.INTL(A2,1,"1111100")

These are the two straightforward methods to rapidly auto-fill a series of weekday-only dates. Use Autofill’s built-in Fill Weekdays option if you need a quick and straightforward solution.

Additionally, you may use the formula technique if you want greater control over which days should be regarded as working days and which should be regarded as weekends.