This guide will discuss how to autofill days of the week based on dates in Excel using three simple and efficient methods.
Excel has so many features and tools for us to utilize. And one of these is the ability to input dates automatically. But, there are times when we need to automatically fill up specific days of the week instead of just dates in our Excel worksheet.
But, manually typing all of the needed dates based on the data is quite inefficient, time-consuming, and inconvenient. So it would serve us better to learn different easy and simple methods of how to autofill days of the week based on data in Excel.
Furthermore, we will be utilizing the many features, tools, and functions Excel has available for us to perform these tasks.
Let’s take a sample scenario.
Suppose you are creating a project timeline for your team. But, you only need the dates for weekdays. So you are looking for a way to autofill only the weekdays needed for the project timeline.
Great! Before we start learning the different methods, let’s first understand the syntax of the functions we will be using.
The Anatomy of the WORKDAY Function
The syntax or the way we write the WORKDAY function is as follows:
=WORKDAY(start_day;days;[holidays])
Let’s take apart this formula and understand what each term means:
- = this is how we start any function in Excel.
- WORKDAY() is our
WORKDAYfunction. This function is used to generate past or future working days. - start_date refers to the starting date. And this is a required argument.
- days is also a required argument. And it refers to the working days before or after the start_date. We also use positive numbers for future dates and negative ones for past dates.
- holidays is an optional argument. And it refers to a list of dates that need to be considered non-working days.
The Anatomy of the SEQUENCE Function
The syntax or the way we write the SEQUENCE function is as follows:
=SEQUENCE(rows;[columns];[start];[step])
Let’s dissect this formula and understand what each term means:
- = the equal sign is how we activate any function in Excel.
- SEQUENCE() refers to the
SEQUENCEfunction. So this function is used to create a sequence of numeric values in several formats. - rows is a required argument. And it refers to the number of rows.
- columns refer to the number of columns. And it is an optional argument.
- start is another optional argument. So it refers to the starting number in the return array.
- step refers to the difference between two successive values in the sequence of numbers. Also, this is an optional argument.
Awesome! We have learned the syntax of these two functions. Now let’s move on and discuss the different methods of how to autofill only weekday dates in Excel.
Using the WORKDAY function and the SEQUENCE function
The first method we will try to autofill only weekday dates in Excel is by using the WORKDAY function and the SEQUENCE function, which we discussed previously.
So we will use the WORKDAY function to get the date’s serial number before and after the given number of workdays. Then, we will use the SEQUENCE function to return a sequence of numbers.
To use this method, follow the steps mentioned below:
1. Firstly, we need to select the range of cells where we will input the workday dates. Then, right-click and select Number Format.
2. Secondly, the Number Format window will appear. So select Date and choose the Type “Wednesday, March 14, 2012“. Then, click OK to apply the changes.
3. Thirdly, select a cell and input the formula “=WORKDAY(C2-1,SEQUENCE(5))”. Lastly, press the Enter key to return the results.
4. And tada! We have successfully autofill weekday dates using the WORKDAY and SEQUENCE functions.
Using the WORKDAY.INTL and SEQUENCE functions
In the second method, we will utilize the WORKDAY.INTL function and SEQUENCE function. Contrary to the previous methods, there will be times when we want to customize the weekends according to our preferences.
So the WORKDAY.INTL function allows us to customize specific weekend dates according to our standards. And to do this, let’s follow the steps below:
1. Firstly, we need to select the column containing our dates. Then, right-click and select Number Format.
2. Secondly, the Number Format window will open. So select the Date category and click the Type “Wednesday, March 12, 2012”. Then, click OK to apply the changes.
3. Next, select cell C3 and input the formula “=WORKDAY.INTL(C2-1,SEQUENCE(5),7)”. Lastly, press the Enter key to return the results.
4. And tada! We have successfully applied the second method to our worksheet.
Using the TEXT function
Our last method involves the use of the TEXT function. So the TEXT function will return a value to text in a specific number format. And the idea would be to convert the dates to the days of the week using the dddd format in Excel. So the dddd format refers to the full name of the days of the week.
To do this third method, let’s learn the step-by-step process.
1. Firstly, select cell D3 in the data set. Then, type in the formula “TEXT(C3;”dddd)”. Lastly, press the Enter key to apply the changes.
2. Secondly, drag down the first row to autofill and complete the series of days.
3. And tada! We have successfully auto-filled only weekday dates in Excel using the TEXT function.
You can make your own copy of the spreadsheet above using the link attached below.
And that’s pretty much it! We have discussed three simple and efficient methods of how to autofill only weekday dates in Excel. Now you can choose any of the methods and apply them to your work whenever necessary.
Are you interested in learning more about what Excel can do? You can now use the WORKDAY function and SEQUENCE function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
