How to Autofill Days of Week Based on Date in Excel (5 Easy Ways)

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.

Autofill Days of Week Based on Date in Excel


Download Practice Workbook

To practice by yourself, download the following workbook.


5 Ways to Autofill Days of Week Based on Date in Excel

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 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 tool when we need to feel 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.

Excel Format Cells Feature & AutoFill Tool to Autofill Days of Week Based on Date

  • 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.

Excel Format Cells Feature & AutoFill Tool to Autofill Days of Week Based on Date

NOTE:dddd’ denotes the full name of the day of the week.

  • 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 Autofill Dates 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.

Use Fill Feature to Autofill Only Weekdays Based on Date

  • 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.

Use Fill Feature to Autofill Only Weekdays Based on Date

NOTE:dddd’ denotes the full name of the day of the week.

  • Now, select the range of cells to work with.

Use Fill Feature to Autofill Only Weekdays Based on Date

  • Subsequently, in the Editing group under the Home tab, select Series from the Fill drop-down list.

Use Fill Feature to Autofill Only Weekdays Based on Date

  • 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: Applications of Excel Fill Series


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).

Autofill Workdays Based on Date with Excel WORKDAY & SEQUENCE Functions

  • Next, right-click on the mouse and select Format Cells.

Autofill Workdays Based on Date with Excel WORKDAY & SEQUENCE Functions

  • 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.

Autofill Workdays Based on Date with Excel WORKDAY & SEQUENCE Functions

  • 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.

NOTE: Here, Excel assumes Saturday and Sunday as weekends.

🔎 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 Enter Sequential Dates Across Multiple Sheets in Excel


Similar Readings


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.

Apply WORKDAY.INTL & SEQUENCE Functions in Excel to Autofill Workdays with Custom Weekend Parameter

  • Then, right-click on the mouse. Here, select Format Cells.

Apply WORKDAY.INTL & SEQUENCE Functions in Excel to Autofill Workdays with Custom Weekend Parameter

  • 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.

Apply WORKDAY.INTL & SEQUENCE Functions in Excel to Autofill Workdays with Custom Weekend Parameter

  • Consequently, you’ll see the change in your datasheet.

Apply WORKDAY.INTL & SEQUENCE Functions in Excel to Autofill Workdays with Custom Weekend Parameter

  • Now, select cell C6 and type the formula:
=WORKDAY.INTL(C5-1,SEQUENCE(5),7)

  • Finally, press Enter and it’ll return the workdays.

NOTE: Friday and Saturday are absent from the list.

🔎 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 also the dates below it to the days of the week. Therefore, follow the steps to autofill the days.

Excel TEXT Function for Auto Filling Days of Week Based on Date

STEPS:

  • First of all, select cell D5 and type the formula:
=TEXT(C5,"dddd")

Excel TEXT Function for Auto Filling Days of Week Based on Date

  • Then, press Enter. And eventually, use the AutoFill tool to complete the series.


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

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo