Sometimes we need to have days of the week instead of only dates in our Excel datasheet. But, manually typing the days based on date is quite inconvenient. In this article, we will show you the easiest ways to Autofill Days of Week based on Date in Excel.
To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents a Salesman and his Joining Date in a company.
How to Autofill Days of Week Based on Date in Excel: 5 Ways
1. Excel Format Cells Feature & AutoFill Tool to Autofill Days of Week Based on Date
Excel provides numerous Features and Tools for performing different kinds of operations. In our first method, we’ll use the Format Cells feature and the AutoFill tool to autofill the days of the week based on the date. We use the Format Cells feature in Excel to change any kind of formatting related to the cells. And we apply the AutoFill shortcut when we need to fill a series. Therefore, follow the steps below to autofill the days of the week based on the date.
STEPS:
- First, select cell C5 and right-click on the mouse.
- Then, select Format Cells.
- As a result, the Format Cells dialog box will pop out. Here, under the Number tab, select Custom.
- After that, select ‘dddd’ in the Type box and press OK.
- At last, use the AutoFill tool to complete the series. Thus, you’ll get your desired outcome as it’s shown in the following picture.
Read More: How to Repeat Formula Pattern in Excel
2. Use Fill Feature to Autofill Only Weekdays Based on Date
Fill command is another useful feature in Excel. In this case, we’ll use the Fill feature to autofill only the Weekdays. We have to keep in mind that Excel automatically assumes Saturday & Sunday as weekends. So, it’ll fill the series without those days. Now, learn the process to perform the task.
STEPS:
- In the beginning, select cell C5.
- Then, right-click on the mouse.
- A dialog box ‘Format Cells’ will pop out. There, select ‘dddd’ in the Type box from the Custom Category under the Number tab and press OK.
- Now, select the range of cells to work with.
- Subsequently, in the Editing group under the Home tab, select Series from the Fill drop-down list.
- Another dialog box will pop out. Here, select Columns in Series in, Date from Type options, and Weekday from Date unit.
- Afterward, press OK.
- In the end, it’ll return the expected workdays of the week.
Read More: How to AutoFill Months in Excel
3. Autofill Workdays Based on Date with Excel WORKDAY & SEQUENCE Functions
We know Excel provides various Functions and we use them for multiple purposes. Such a kind is the WORKDAY function. We apply this function when we need to return the serial number of the date before or after a stated number of workdays. The SEQUENCE function is another handy function and we use it for returning a sequence of numbers. In this method, we’ll apply both of these functions simultaneously to get our workdays of the week. Hence, follow the below process to know how to get this job done.
STEPS:
- Firstly, select the range of cells (C5:C10).
- Next, right-click on the mouse and select Format Cells.
- Consequently, the Format Cells dialog box will pop out. Here, in the Date Category under the Number tab, select the option pointed in a red-colored arrow in the following picture.
- Then, press OK.
- As a result, you’ll see the name of the day along with the date.
- Now, select cell C6 and type the formula:
=WORKDAY(C5 -1, SEQUENCE(5))
- Lastly, press Enter and it’ll immediately complete the series with the workdays.
🔎 How Does the Formula Work?
- SEQUENCE(5)
This part of the formula returns the sequence of 5 numbers. You can get any number of sequences you want.
- WORKDAY(C5 -1, SEQUENCE(5))
Our start date is C5. Here, we subtract 1 from C5 to have the C5 itself included in the results. This is because the WORKDAY function adds the number of days stated in the second argument.
Read More: How to Repeat Number Pattern in Excel
4. Apply WORKDAY.INTL & SEQUENCE Functions in Excel to Autofill Workdays with Custom Weekend Parameter
In the previous method, the weekends are Saturday and Sunday. But, if we want to set the weekends according to our standard, we have to use WORKDAY.INTL function. This function allows us to customize the weekend according to our wishes. Moreover, we’ll use the SEQUENCE function to return the sequence of numbers. So, learn the steps given below to autofill Workdays with custom weekend parameters in our Excel worksheet.
STEPS:
- First, select the range of cells.
- Then, right-click on the mouse. Here, select Format Cells.
- A dialog box will pop out and there, select the option pointed in a red-colored arrow from the Type box in the following picture in the Date Category under the Number tab.
- After that, press OK.
- Consequently, you’ll see the change in your datasheet.
- Now, select cell C6 and type the formula:
=WORKDAY.INTL(C5-1,SEQUENCE(5),7)
- Finally, press Enter to use this custom autofill formula to get the workdays.
🔎 How Does the Formula Work?
- SEQUENCE(5)
Here, it returns the sequence of 5 numbers or any number of sequences you want.
- WORKDAY.INTL(C5-1,SEQUENCE(5),7)
This part of the formula returns the serial number of the date after a specified number of workdays. In this case, the specified number is a sequence. So, it’ll return the sequence after C5. But, it’ll also include C5 as we subtract 1 from C5. In the argument, 7 is the weekend parameter for Friday and Saturday. So, these days will be omitted.
5. Excel TEXT Function for Auto Filling Days of Week Based on Date
Lastly, we’ll use the TEXT function for auto-filling days of the week based on the date. The TEXT function returns a value to text in a particular number format. In Excel, ‘dddd’ represents the full name of the day of the week. In the following dataset, we’ll convert the Joining Date and the dates below it to the days of the week. Therefore, follow the steps to autofill the days.
STEPS:
- First of all, select cell D5 and type the formula:
=TEXT(C5,"dddd")
- Then, press Enter. And eventually, use the AutoFill tool to complete the series.
Download Practice Workbook
To practice by yourself, download the following workbook.
Conclusion
Henceforth, you will be able to Autofill Days of Week based on Date in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Further Readings
- How to Autofill Dates in Excel Without Dragging
- How to Create Automatic Rolling Months in Excel
- How to Increment Month by 1 in Excel
- How to Enter Sequential Dates Across Multiple Sheets in Excel
- How to Fill Down Blanks in Excel
- How to Add Sequence Number by Group in Excel
- How to Perform Predictive AutoFill in Excel
