Formula for Weekly Dates in Excel (5 Examples)

In this article we will demonstrate various formulas for Weekly Dates in Excel.

We’ll use the dataset below to illustrate our methods, and assign a “Weekly Date” to each article in the dataset in 5 different ways.

Method 1 – Using the WORKDAY Function to Find Weekly Dates in Excel

To start with, we’ll use the WORKDAY function to assign a weekly date to each article, skipping the weekends.

Steps:

• In cell D8, enter the following formula:

In the WORKDAY function, we set C4-1 as start_date and SEQUENCE(C5) as days. The SEQUENCE function will return a list of sequential numbers in an array. The WORKDAY function will then return the next working day.

The SEQUENCE function is only available in Office 365 or later versions of Microsoft Excel.

• Press ENTER to return the result.

Method 2 – Using the IF and WEEKDAY Functions

We can acomplish the same task using the IF function and the WEEKDAY function together.

Steps:

• In cell D7, enter the following formula:

Here, the formula will return the value stored in cell C4, which is the Start Date.

• Press ENTER to return the first Weekly Date.

• Select the next cell, D8.
• Enter the following formula:

• Press ENTER to return the second date in your selected format.

• Drag the Fill Handle down to copy the formula to the rest of the cells in the series.

The output is as follows:

Read More: How to Insert Static Date in Excel

Method 3 – Using the ROWS Function to Find Weekly Dates

Now we’ll use the ROWS function for our weekly dates formula. In the dataset below, the given time for each task is one week. Let’s calculate the End Date and the Start Date, skipping the weekends.

Steps:

• Insert the first Weekly Date of a month as the Start Date.

• In cell D5 (the End Date of the first week), enter the following formula:

Here, the ROWS function will return a sequence of numbers and multiply it by 4, then we add the result to the first date of the week to get the last working day.

• Press ENTER to return the result.

Now we’ll calculate the Start Date for the next week.

• In the cell where you want the date (C6) enter the following formula:

Here, the formula adds 3 to the End Date of the week before, and returns the date of the 3rd day after the End Date.

• Press ENTER to return the result.

• Drag the Fill Handle down to copy the formula to the cells below.

The dataset looks like this:

Now we calculate the End Date for the next weeks.

• In cell D6, enter the following formula:

Here, the ROWS function will return a sequence of numbers and multiply it by 7. We then add the result to the End Date of the week before to get the date 7 days later.

• Press ENTER to return the End Date.

• Drag the Fill Handle down to copy the formula to the cells below.

We have all the Start Date and End Date vales.

Method 4 – Using the SUM Function to Find Weekly Dates

We can also use the SUM function to find Weekly Dates.

Steps:

• In cell C5, enter the first Weekly Date of a month as the Start Date.

Now we enter the End Date of the first week.

• In cell D5, enter the following formula:

Here, the SUM function will add 4 to the value in cell C5 (the Start Date), and return the date 4 days later.

• Press ENTER to get the first End Date.

Now we calculate the Start Date for the next week.

• In cell C6, enter the following formula:

Here, the SUM function will add 3 to the value in cell D5 (the End Date of the week before), and return the date of the 3rd day after that.

• Press ENTER to return the result.

• Drag the Fill Handle down to copy the formula to the cells below.

We have all the Start Date values..

• Select the cell containing the first End Date value, D5.
• Drag the Fill Handle down to copy the formula to the cells below.

We have all the End Date values.

Method 5 – Using the DATE Function for Weekly Dates

Another formula for weekly dates uses the DATE function.

Steps:

• Insert the first Weekly Date of a month as the Start Date in cell C5.

• In cell D5 (the End Date of the first week), enter the following formula:

Here, in the DATE function, we add 4 to the date in cell C5. This formula will return the date 4 days later, which will be the End Date of the week.

• Press ENTER to return the first End Date.

Now we calculate the Start Date for the next week.

• In cell C6, enter the following formula:

Here, in the DATE function, we add 3 to the date in cell D5 (the End Date of the week before) to return the date 3 days later, which will be the Start Date of the week.

• Press ENTER to return the result.

• Drag the Fill Handle down to copy the formula to the cells below.

We have all the Start Date values.

• Select cell D5.
• Drag the Fill Handle down to copy the formula to the cells below.

We have the Start Date and End Date for the required weeks, skipping the weekends.

Read More: How to Remove Time from Date in Excel

How to Use AutoFill For Weekly Dates in Excel

We can obtain weekly dates in Excel without using a formula.

Steps:

• Insert the first Weekly Date.

• Drag the Fill Handle to get the dates sequentially.

• Click on Auto Fill Options.

A drop-down menu will appear.

• Select Fill Weekdays.

Now only Weekly Dates are on this list, not weekends.

Things to Remember

• Since the SEQUENCE function returns an array, it does not work in Excel 2019 or any earlier version, as they don’t support dynamic arrays.

Download Practice Workbook

Related Articles

• How to Display Day of Week from Date in Excel
• How to Insert Last Saved Date in Excel
• How to Insert Drop Down Calendar in Excel

<< Go Back to Insert Date | Date-Time in Excel | Learn Excel