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.


Download Practice Workbook


5 Simple Formula for Weekly Dates  in Excel

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.

Formula for Weekly Dates in Excel


1. Using 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 week dates for each article and skip the weekends.

Using WORKDAY Function for Weekly Dates in Excel

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.

Read More: How to Display Day of Week from Date in Excel (8 Ways)


2. Applying Formula Combining 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

Applying IF and WEEKDAY Functions

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

  • Thirdly, press ENTER to get the value.

Using IF and WEEKDAY Functions in Weekly Dates Formula in Excel

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

Dragging Fill Handle to Copy Weekly Dates Formula in Excel

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

Read More: How to Combine Date and Time in One Cell in Excel (4 Methods)


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.

Use of ROWS Function for 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.
=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

Adding Number in Weekly Date Formula in Excel

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

ROWS Function in Formula for Weekly Dates in Excel

Here, the ROWS function will return a sequence of numbers and multiply it with 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.

Dragging Fill Handle to Get End Date in Weekly Dates Formula in Excel

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

Read More: How to Change Dates Automatically Using Formula in Excel


Similar Readings


4. Employing SUM Function to Get Weekly Dates in Excel

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

Steps:

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

Employing SUM Function for Weekly Dates Formula in Excel

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

SUM function to Calculate Start Date in Weekly Dates Formula in Excel

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 Excel Formula (8 Ways)


5. Use of DATE Function

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.

DATE Function in Weekly Dates Formula in Excel

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.

DATE Function in Weekly Dates Formula in Excel to Calculate the Start Date

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

Dragging Fill Handle to get End Date in Formula for Weekly Dates in Excel

Read More: How to Insert Day and Date in Excel (3 Ways)


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

Using AutoFill Options For Weekly Dates

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

Read More: Excel Automatically Enter Date When Data Entered (7 Easy Methods)


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.

Practice Section

Here, I have provided a practice sheet for you to practice the weekly dates formula in Excel.

Practice Sheet for Weekly Dates Formula in Excel


Conclusion

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


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, 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

Leave a reply

ExcelDemy
Logo