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

We’ll use a sample dataset as an example. For instance, the following dataset represents a Salesman and his Joining Date in a company. We will Autofill the Days of the Week based on the Date in Excel.

How to Autofill Days of the Week Based on a Date in Excel: 5 Ways

Method 1 – Using the Excel Format Cells Feature and the AutoFill Tool to Autofill Days of the Week Based on the Date

Steps:

• Select cell C5 and right-click on it.
• Select Format Cells.

• The Format Cells dialog box will pop out. Under the Number tab, select Custom.
• Put ‘dddd’ in the Type box and press OK.

• Use the AutoFill tool to complete the series.

Read More: How to Repeat Formula Pattern in Excel

Method 2 – Use the Fill Feature to Autofill Only Weekdays Based on a Date

Steps:

• Select cell C5.
• Right-click on it.
• Select Format Cells.

• A dialog box Format Cells will pop out. Put ‘dddd’ in the Type box from the Custom Category under the Number tab and press OK.

• Select the range of cells to work with.

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

• Another dialog box will pop out. Select Columns in Series in, Date from Type options, and Weekday from Date unit.
• Press OK.

• This will return the workdays of the week.

Read More: How to AutoFill Months in Excel

Method 3 – Autofill Workdays Based on a Date with Excel WORKDAY and SEQUENCE Functions

Steps:

• Select the range of cells (C5:C10).

• Right-click on the range and select Format Cells.

• The Format Cells dialog box will pop out. In the Date Category under the Number tab, select the option for the full date.
• Press OK.

• You’ll see the name of the day along with the date.

• Select cell C6 and insert this formula:

• 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

Method 4 – Apply WORKDAY.INTL and SEQUENCE Functions in Excel to Autofill Workdays with a Custom Weekend

Steps:

• Select the range of cells.

• Right-click on it and select Format Cells.

• In the dialog, select the full date type.
• Press OK.

• You’ll see the full date in your datasheet.

• Select cell C6 and insert this formula when Friday and Saturday are weekends (here’s the full list of the last argument and the corresponding weekends)

• Press Enter to get the workdays.

How Does the Formula Work?

• SEQUENCE(5)

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

Method 5 – Use the Excel TEXT Function for Auto-Filling Days of the Week Based on a Date

In the following dataset, we’ll convert the Joining Date and the dates below it to the days of the week.

Steps:

• Select cell D5 and insert this formula:

• Press Enter.
• Use the AutoFill tool to complete the series.

Download the Practice Workbook

Further Readings

• 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

<< Go Back to Autofill Dates | Excel Autofill | Learn Excel