Avoid weekends when autofilling

Sometimes you probably need to fill a column with dates in Excel but you want to avoid weekends, maybe because you want to create a column with working dates. This time I will show you a very simple trick that you can use to avoid having to manually delete the dates of the weekends after autofilling a column with dates.

The first step is to enter the starting date and then copy the data down. In this example I’ll drag the lower right corner of cell A1 to autofill the dates in column A:

Avoid weekends when autofilling

The moment you release the mouse on the last filled cell, a small icon will appear in the lower right corner. You will quickly identify it because the button will have a “+” symbol. This button will give us some autofill options.

Auto-fill working day dates in Excel

Delete weekend dates

When you click on the fill options button, a pop-up menu will appear and you must select the option Fill days of the week.

Automatic list of working days in Excel

They will be immediately removed from the list on Saturdays and Sundays.

List of working days in Excel

Consider that when using this command, Excel will not reduce the number of cells with data but will eliminate the weekend dates and the remaining cells will be filled with days of the week to continue having the same number of cells with dates.

In our example, the previous two images show that the data series ended in row 24 with the date “”. That list still included Saturdays and Sundays but once we ran the command , we will continue to have 24 data but now the dates will end until today “”Because the weekends will have been eliminated and the rest of the cells will have been filled with the subsequent working days.

How to create a list of working dates in Excel

With this method you can quickly create lists with business day dates without the need to manually remove the weekend dates.

Leave a Comment