Formula for Weekly Dates in Excel (5 Examples)

The main focus of this article is to explain formula for weekly Dates in Excel. If you are looking for formulas for weekly dates, then this article will be helpful for you.

Here, I have taken the following dataset to explain this article. This dataset contains “Employee Name” and “Article ID”. Each article is assigned to a “Weekly Date”. I will show you 5 different ways to calculate the weekly dates formula in Excel.

1. Use the WORKDAY Function to Find Weekly Dates in Excel

In this method, I will use the WORKDAY function in the weekly dates formula in Excel. Here, I will assign weekly dates for each article and skip the weekends.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want your first Weekly Date. Here, I selected cell D8.
• Secondly, in cell D8 write the following formula.

Here, in the WORKDAY function, I selected C4-1 as start_date and SEQUENCE(C5) as days. The SEQUENCE function will return a list of sequential numbers in an array. And, the WORKDAY function will return the next working day.

You have to remember SEQUENCE function only works on Office 365 or higher versions of Microsoft Excel.

• Thirdly, press ENTER and you will get the result.

2. Applying Weekly Dates Formula with IF and WEEKDAY Functions

In this method, I will the IF function and the WEEKDAY function for the weekly dates formula in Excel. Let’s see the steps.

Steps:

• Firstly, select the cell where you want to insert the first Weekly Date. Here, I selected cell D7.
•  Secondly, in cell D7 write the following formula.

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

• Thirdly, press ENTER to get the value.

• After that, select the next cell. Here, I selected cell D8.
• Next, in cell D8 write the following formula.

• Thirdly, press ENTER and you will get your date in your selected format.

• After that, drag the Fill Handle to copy the formula.

Here, you can see I have copied the formula to all the other cells.

Read More: How to Insert Static Date in Excel

3. Use of ROWS Function to Find Weekly Dates in Excel

In this method, I will use the ROWS function for the weekly dates formula in Excel. Here, I have taken the following dataset for this method. Here, the given time for each task is one week. I will calculate the End Date and the Start Date and skip the weekends.

Let’s see the steps.

Steps:

• Firstly, insert the first Weekly Date of a month as the Start Data.

• Secondly, select the cell where you want the End Date of the first week. Here, I selected cell D5.
• Thirdly, in cell D5 write the following formula.

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

• Finally, press ENTER to get the result.

Now, I will calculate the Start Date for the next week.

• Firstly, select the cell where you want the date.
• Secondly, in that cell write the following formula.

Here, the formula will sum 3 with the End Date of the week before and return the date of the 3rd day after the End Date.

• Thirdly, press ENTER to get the result.

• After that, drag the Fill Handle to copy the formula.

In the following image, you can see that I have copied the formula to all the other cells.

Now, I will calculate the End Date for the next weeks.

• Firstly, select the cell where you want the End Date. here, I selected cell D6.
• Secondly, in cell D6 write the following formula.

Here, the ROWS function will return a sequence of numbers and multiply it by 7. Then, sum the result with the End Date of the week before to get the date after 7 days.

• Thirdly, press ENTER to get the End Date.

• Now, drag the Fill Handle to copy the formula.

Finally, I have copied the formula and got all the Strat Date and End Date.

4. Using the SUM Function to Get Weekly Dates

Here, I will explain how you can use the SUM function for the Weekly Dates formula in Excel. Let’s see the steps.

Steps:

• Firstly, insert the first Weekly Date of a month as the Start Data.

• Secondly, select the cell where you want the End Date of the first week. Here, I selected cell D5.
• Thirdly, in cell D5 write the following formula.

Here, the SUM function will sum 4 with the value in cell C5 which is the Start Date, and return the date of the 4th day.

• After that, press ENTER to get the End Date.

Now, I will calculate the Start Date for the next week.

• Firstly, select the cell where you want the date.
• Secondly, in that cell write the following formula.

Here, the SUM function will sum 3 with the value in cell D5 which is the End Date of the week before, and return the date of the 3rd day after that.

• Thirdly, press ENTER to get the result.

• After that, drag the Fill Handle to copy the formula.

Now, you can see that I have copied the formula to all the other cells.

• After that, select the first cell where you wrote the formula for the End Date. Here, I selected cell D5.
• Next, drag the Fill Handle to copy the formula.

Here, you can see that I have copied the formula to all the other cells.

Read More: How to Insert Date in Footer in Excel

5. Use of DATE Function for Weekly Dates

In this method, I will show you another formula for weekly dates in Excel using the DATE function. Let’s see how it is done.

Steps:

• Firstly, insert the first Weekly Date of a month as the Start Data.

• Secondly, select the cell where you want the End Date of the first week. Here, I selected cell D5.
• Thirdly, in cell D5 write the following formula.

Here, in the DATE function, I summed 4 with the date in cell C5. This formula will return the date after 4 days which will be the End Date of the week.

• Finally, press ENTER and you will get the End Date.

At this point, I will calculate the Start Date for the next week.

• Firstly, select the cell where you want the Start Date. Here, I selected cell C6.
• Secondly, in cell C6 write the following formula.

Here, in the DATE function, I summed 3 with the date in cell D5 which is the End Date of the week before. This formula will return the date after 3 days which will be the Start Date of the week.

• Thirdly, press ENTER to get the result.

• Now, drag the Fill Handle to copy the formula to other cells.

In the following picture, you can see that I have copied the formula to all the other cells.

• After that, select the first cell where you wrote the formula for the End Date. Here, I selected cell D5.
• Next, drag the Fill Handle to copy the formula.

Finally, you can see I have copied the formula to all the other cells and got the Start Date and End Date of weeks, and skipped the weekends.

Read More: How to Remove Time from Date in Excel

How to Use AutoFill Options For Weekly Dates in Excel?

In this section, I will explain how you can get weekly dates in Excel without using any formula. Let’s see the steps.

Steps:

• Firstly, insert the first Weekly Date.

• Secondly, drag the Fill Handle to get the dates sequentially.

• Thirdly, click on Auto Fill Options.

Here, a drop-down menu will appear.

• After that, select Fill Weekdays.

Finally, you will see only Weekly Dates are on this list without the weekends.

Things to Remember

• It should be noted that the SEQUENCE function returns an array and for this reason, it does not work in Excel 2019 or any earlier version as they don’t support dynamic arrays.

Download Practice Workbook

Conclusion

To conclude, in this article, I tried to explain the formula for weekly dates in Excel. Here, I used 5 different functions to explain 5 different methods. I hope this article was helpful to you. Lastly, if you have any questions let me know in the comment section below.

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