This is usually needed when you’re creating a project plan, where only the weekday dates can be used, and the weekend dates are holidays. In this short tutorial, I am going to show you two simple ways to quickly autofill weekday dates in a column in Excel. We’ll first look at an inbuilt feature in excel that can quickly do this value, and then we will also learn about a formula that you can use to get only the weekday dates. Let’s dive in!
Using the ‘Fill Weekday’ Option in Autofill
Microsoft Excel has an inbuilt auto-fill option called “Fill Weekdays” that allows you to quickly fill a sequence of weekday dates only. Let me show you how it works. Below I have a date in cell A1, and I want to fill the cells below A1 with only the weekday dates (i.e. Monday to Friday). Here are the steps to do this: The above steps would instantly fill the column with a sequence of dates and occur on weekdays only. If you do not see the Auto Fill icon, you can also get the same thing done by selecting the cells, going to the Home tab, in the Editing group clicking on the Fill option, and then clicking on the Series option. This will open the Series dialog box where you can select the Weekdays option to fill the entire column with the weekday dates only. While this is a quick and easy way to quickly fill a column with only the weekday dates, remember that it will consider Monday to Friday as the weekday dates, and Saturday and Sunday as the weekend dates. It does not give you the flexibility to choose the days you want to consider weekdays. For example, if you want the weekdays to be dates that occur from Monday to Saturday, and only Sunday as the weekend date, then you can not use this method. In that case, you’re better off using the formula method covered later in this tutorial.
Fill Handle Not Showing – How to Fix?
When I shared this trick on my YouTube channel, some people reported that they cannot see the fill handle when they select a cell (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 facing the same problem, here is how to fix it: The fill handle should now be available to use in the worksheet.
Using Formula to Get Weekday Dates Only
If you want to fill a column with the dates that occur on specific days, you can easily do that using the WORKDAY.INTL function. WORKDAY.INTL function returns the date after a specified number of days, and it also allows you to choose the days in the week that are acceptable. Let me first show you how it works and then I’ll explain the formula in detail. Below I have a date in column A1 and I want to fill the column with dates that occur on weekdays only (where we give would be Monday to Friday). Here is a formula that will do this for me: Once I have this formula in cell A2, I can copy it for all the other cells in the column. How does this formula work? WORKDAY.INTL function takes 3 arguments: The magic happens in the third argument where I have used “0000011” There are 7 digits in the double quotes, where each digit specify whether that day should be considered a weekday or a weekend. 0 means that it would be a weekday and 1 means it would be a weekend. 0000011 means that the first five days of the week (which would be Monday to Friday) would be considered weekdays, and the last two days of the week (which would be Saturday and Sunday) would be considered weekend days. So when the WORKDAY.INTL function is giving me the next date, it makes sure that only the working days are returned, and weekends are not. The best thing about this formula is that you can specify any day as a working day or a non-working day. So if you want to fill a column with dates that occur only on Monday, Wednesday, and Friday, you can modify the third argument in the formula as shown below: You can also modify the above formula to give you only the weekend dates as well. to do that, you will have to change the third argument to make the first five days as non-working, and make the last two days as working. So these are two simple ways you can use to quickly auto-fill a sequence of weekday dates only. if you’re looking for a super quick and easy solution, you can use the inbuilt Fill Weekdays option in Autofill. And if you need more control over what days should be considered working and what days should be considered weekend days, you can use the formula method. I hope you found this tutorial useful. Other Excel tutorials you may also like:
Check IF a Date is Between Two Given Dates in ExcelExcel WEEKDAY FunctionHow to Highlight Weekend Dates in Excel?Get Day Name from Date in Excel (Easy Formulas)How to Get the First Day of the Month in ExcelHow to Add or Subtract Days to a Date in ExcelHow to Quickly Insert Date and Timestamp in ExcelHow to Quickly Fill Numbers in Cells without DraggingHighlight Dates Before Today in Excel (An Easy Guide)
