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:

`=WORKDAY(C4-1,SEQUENCE(C5))`

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:

`=C4`

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:

`=IF(WEEKDAY(D7,2)=5,D7+3,D7+1)`

**Formula Breakdown**

**WEEKDAY(D7,2) —->**Returns the day number in a week, with numbers**1**(Monday) to**7**(Sunday).**Output: 1**

**IF(WEEKDAY(D7,2)=5,D7+3,D7+1) —->**resolves to**IF(1=5,D7+3,D7+1) —->**Performs the**logical_test**. If it is**TRUE**then the function will return the day**2**days later, and if it is**FALSE**then the function will return the day after the selected day.**Output: 44775**

- 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:

`=C5+ROW($A$1:A1)*4`

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:

`=D5+3`

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:

`=$D$5+ROWS($A$1:A1)*7`

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:

`=SUM(C5,4)`

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:

`=SUM(D5,3)`

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:

`=DATE(YEAR(C5),MONTH(C5),DAY(C5)+4)`

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:

`=DATE(YEAR(D5),MONTH(D5),DAY(D5)+3)`

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
**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**