Weekend, this word actually feels pleasant to those who earn dark circles during weekdays in the office. The same word feels like an extra burden when you have to fill only weekend or weekday dates in an Excel sheet.
Sometimes we need Excel records based on weekends or weekdays. In different sectors like tourism, banking, institutes, etc., filling dates by weekdays or weekends is a common task. But, if you are thinking to do it manually, it might take hours. So, this article will guide you on how to autofill dates in Excel on weekdays or weekends.
There are actually some built-in functions in Excel like WORKDAY and WORKDAY.INTL that you can use to autofill weekday dates excluding weekends or just weekend dates excluding all weekdays. These methods are suitable for all versions of excel, which are higher than in 2007.
How to Auto-Fill Weekend Dates in Excel?
In general, Saturdays and Sundays are considered weekend dates. These days are non-working days and sometimes we need this data for important analysis or calculation. It would be difficult if you are filling it out manually.
Method 1
You can autofill dates by using WORKDAY.INTL function. Follow the step-by-step procedure:
- Open the Excel sheet and select A1 cell. Enter a start date in cell A1. (Ex: 21-10-2022)
- Select cell A2 and click on the fx label.
- A new box will open. Choose the Date & Time from the category section and select WORKDAY.INTL function from Select a function section.
Alternatively, type the equal sign in A2 then some functions will appear in the name box. The name box is located in the left corner of the formula bar. You can select WORKDAY.INTL function from here also.
- After selecting WORKDAY.INTL function another box will open. You will need to fill in function arguments (Start Date, Day, Weekends, etc.)in this box. We have already filled the start date in the A1 cell so instead of typing a date we can just type A1, insert 1 in the Days box and insert “1111100” (with quotes) in the Weekend box. Here we are not calculating holidays so leave it blank.
- Check if the argument result is showing a numeric value like 4482 and if the formula result is showing a date. And then click Ok.
- Now, change the display format of the date according to your choice. You need to do this from the taskbar using the Number Format box as shown in the following picture.
- Once done, drag the A2 cell to the bottom to autofill dates in other cells. As you can see in the following screenshot that only weekend dates are auto-filled in all the other cells.
This method could be a bit longer. To make it easier you can directly insert this formula in the A2 cell or in the formula bar after filling start date in A1.
=WORKDAY.INTL(A1,1,"1111100")
So, this is the very first method to autofill weekend dates in your Excel sheet. There is one more method to fill weekend dates in Excel that I am going to share below in this article.
Method 2
If you are unable to remember the formula or don’t want to use this method, here is another alternative method for you. You can fill in weekend dates using this method. Here are the steps:
- Fill Start Saturday date in A1. (Ex: 22-10-2022)
- Fill Start Sunday date in A2. (Ex: 23-10-2022)
- Select A3 and type the following formula:
=A1+7
- Select A4 and type the following formula:
=A2+7
- And then, drag down cell A4 to autofill dates in all other cells. You will see that all dates in the column are weekend dates.
You might have tried other methods. Like in some older versions of Excel, there was an autofill option where you could select a weekend option from the dropdown and filter dates as a weekend only, but this option is no longer available in the latest versions.
How to Autofill Weekday Dates in Excel Excluding Weekends?
Above I have shared simple and accurate methods to autofill weekend dates only. Sometimes we need to fill workdays excluding weekends (Mon-Fri). If you don’t know how to autofill dates in Excel excluding weekends, check the below-mentioned methods to autofill weekday dates excluding weekends.
Method 1
This method allows you to fill workdays dates by the drag and autofill option.
- Enter the start date in the A1 cell.
- Drag down to fill normal dates in the column.
- Click on the Autofill option in the bottom right corner. (It looks like a small square)
- A new box will open with different fill options. Select the Fill Weekdays option from here.
- Now only weekday dates are available in your column without weekend dates.
Method 2
In this method, you can autofill weekday dates by making use of the Fill option that can be found in the Editing area of the taskbar. Follow these steps carefully.
- Open the Excel sheet, fill start date in the A1 cell, and drag it down to fill in the dates.
- Click on the Fill option. Select Series. A popup box will open.
- Select Columns as Series in, Date as Type, and Weekday as Date unit.
Method 3
You can autofill only weekdays excluding weekend dates by using binary digits and WORKDAY.INTL function. Same as we did while retrieving weekend days, replace “1111100” with “0000011” in the formula.
=WORKDAY.INTL(A1,1,"0000011")
Seven digits are for 7 days of the week, the same as in the formula for weekend dates. The occurrence of 1 digit shows that you don’t want the value of these two days, and 0 represents the required value or date.
Here are the steps you need to follow:
- Open the Excel sheet and enter the start date in the A1 cell.
- Check the taskbar that the Number format is selected as Date (short date or long date) instead of General.
- Select cell A2 then click on the fx label near the formula bar.
- A new box will open where you will need to choose the Date & Time category and select WORKDAY.INTL function.
- Now fill function arguments the same as we filled in the weekend date process.
- A1 cell contains the value of the start date so we will fill A1 in the Start Date.
- Type 1 in the day box and insert “0000011” (with quotes) in the weekend box.
- Leave holidays blank.
- Click Ok.
Now, you just need to drag down the A1 cell to autofill weekday dates excluding weekends.
Method 4
There is one more alternative way of using the WORKDAY.INTL to achieve the weekday dates. Another method of excluding weekend dates and filling only workdays without weekends is by using this formula.
=WORKDAY.INTL(A1,1,1)
Make sure that you are not doing these mistakes while implementing this formula:
- You should not select WORKDAY.INTL function from name box or fx label. You have to manually type it.
- The start date should be in the same column in which you will display workday dates. The cell address of another column will not work.
- While passing arguments make sure that you are selecting Saturday and Sunday and their value will reflect as 1 in the formula. Numeric 1 will not work here.
Now, follow the below-mentioned steps:
- Fill in a start date in cell A1.
- Click cell A2 and type this formula. Make sure that you have the Number Format as Date from the taskbar.
=WORKDAY.INTL(A1,1,1)
- Now, as soon as you will click the A3 cell, the above formula will be converted into a date.
- Finally, drag down the A2 cell to fill weekday days excluding all the weekends.
Method 5
You can use the WORKDAY function to fill only weekday dates in an excel sheet. This formula will exclude weekend dates (Saturday and Sunday). The formula reads as below:
=WORKDAY(A1,1)
All you need to enter the start date in the A1 cell and apply this formula in the A2 cell directly or you can fill in the argument values to achieve the same thing by following the steps mentioned below:
- Insert a start date in A1.
- Select the A2 cell.
- Click the fx label. Insert the WORKDAY function from the Date and Time category.
- Fill argument values as A1 in the Start Date and 1 in the Day field and click OK.
- At last, drag the A2 cell down to autofill dates. All the dates will be workdays excluding weekends.
Conclusion
These are proven ways to autofill weekend or weekday dates in Microsoft Excel. As discussed earlier, all these methods are suitable for all versions of excel above 2007. These are easy-to-implement methods that will display accurate results.
Even if you are not a frequent user of Excel or you are a beginner, you can easily try the above-mentioned methods because every method is described step-by-step. All the methods mentioned above are suitable for maintaining large or short records.
