# Formula for Weekly Dates in Excel (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.
`=WORKDAY(C4-1,SEQUENCE(C5))` 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.
`=C4` 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.
`=IF(WEEKDAY(D7,2)=5,D7+3,D7+1)` Formula Breakdown

• WEEKDAY(D7,2) —-> Here, the WEEKDAY function will return the day number in a week with numbers 1 (Monday) to 7 (Sunday).
• Output: 1
• IF(WEEKDAY(D7,2)=5,D7+3,D7+1)  —-> turns into
• IF(1 =5,D7+3,D7+1) —-> Here, the IF function will check for the logical_test. If it is TRUE then the function will return the day after 2 days and if it is FALSE then the function will return the next day after the selected day.
• Output: 44775
• 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.
`=C5+ROW(\$A\$1:A1)*4` 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.
`=D5+3` 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.
`=\$D\$5+ROWS(\$A\$1:A1)*7` 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.
`=SUM(C5,4)` 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.
`=SUM(D5,3)` 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.
`=DATE(YEAR(C5),MONTH(C5),DAY(C5)+4)` 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.
`=DATE(YEAR(D5),MONTH(D5),DAY(D5)+3)` 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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Mashhura Jahan

Hey! Welcome to my profile. Currently, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  