## Calculate the number of weeks between given dates

This article demonstrates Excel formulas that calculate complete weeks between two given dates and weeks and days between two given dates.

#### Table of Contents

## 1. Calculate the number of weeks between given dates

The image above demonstrates a formula that calculates the number of complete weeks between two dates. Column B and column C contains random dates.

Formula in cell D3:

Copy cell D3 and paste to cells below as far as needed.

### 1.1 Explaining formula in cell D3

This formula works fine if the start is later than the end date, however, you get a minus sign before the number.

If you want to remove the minus sign simply use the ABS function to remove it, the formula then becomes:

#### Step 1 - Subtract dates

C3-B3

becomes

35067 - 35685 equals 618 days.

#### Step 2 - Divide with 7

There are seven days in a week so we need to divide the result with 7.

(C3-B3)/7

becomes

618/7 equals 88.28571429.

#### Step 3 - Round the number down

The ROUNDDOWN function rounds the number down.

ROUNDDOWN(*number*, *num_digits*)

ROUNDDOWN((C3-B3)/7)

becomes

ROUNDDOWN(88.28571429) and returns 88.

## 2. Calculate the number of weeks and days between given dates

Formula in cell D3:

### 2.1 Explaining formula

#### Step 1 - Calculate days between dates in cells C3 and B3

The minus sign lets you subtract numbers in an Excel formula.

C3-B3

becomes

45339-44522

and returns 817.

#### Step 2 - Calculate weeks

The division character lets you divide numbers in an Excel formula. The parentheses let you control the order of operation, we want to subtract before we divide.

(C3-B3)/7

becomes

817/7

and returns approx. 116.71

#### Step 3 - Remove decimals

The INT function removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.

INT(*number*)

INT((C3-B3)/7)

becomes

INT(116.71)

and returns 116

#### Step 4 - Concatenate number and string

The ampersand character lets you concatenate values in an Excel formula. Use double quotes with text values to avoid a formula #NAME error.

INT((C3-B3)/7)&" weeks "

becomes

116&" weeks"

and returns 116 weeks.

#### Step 5 - Calculate the remainder

The MOD function returns the remainder after a number is divided by a divisor.

MOD(*number*, *divisor*)

MOD(C3-B3, 7)

becomes

MOD(817, 7)

and returns 5.

#### Step 6 - Concatenate numbers and text values

The ampersand character concatenates values in an Excel formula.

INT((C3-B3)/7)&" weeks "&MOD(C3-B3,7)&" days"

becomes

116&" weeks "&5&" days"

and returns 116 weeks 5 days.

## 3. Calculate the number of weeks and days between given dates - dynamic text values

Excel 365 formula in cell D3:

### Explaining formula

#### Step 1 - First argument *expression*

The SWITCH function returns a given value determined by an expression and a list of values. The SWITCH function is made for exact matches, however, there is a workaround to use larger than and smaller than characters.

If any of the value arguments returns a value equal to the expression argument the corresponding result argument is returned.

SWITCH(*expression*, *value1*, *result1*, [*default* or *value2*, *result2*],…[*default* or *value3*, *result3*])

TRUE and FALSE are boolean values, they are often the result of a logical test. I am going to use TRUE in this *expression* argument.

#### Step 2 - Second argument *value1*

The following formula calculates the remaining days after we subtract two Excel dates and then divide by seven, there are seven days in one week.

The MOD function returns the remainder after a number is divided by a divisor.

MOD(*number*, *divisor*)

MOD(C3-B3,7)=0

becomes

MOD(44522-45334, 7)=0

becomes

MOD(812, 7)=0

becomes

0=0

and returns TRUE. This value matches the expression argument, the formula will now return the result argument.

#### Step 3 - Third argument *result1*

The INT function removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.

INT(number)

INT((C3-B3)/7)&" weeks "

becomes

INT((812)/7)&" weeks "

becomes

INT(116)&" weeks "

becomes

116&" weeks "

and returns "116 weeks" in cell D3.

There are two more value arguments:

MOD(C3-B3,7)=1 adds day to the result. The remainder is one.

MOD(C3-B3,7)>1 adds days to the result. The remainder is more than one.

#### Step 4 - Shorten the formula

The LET function allows you to name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(*name1*, *name_value1*, *calculation_or_name2*, [*name_value2*, *calculation_or_name3*...])

SWITCH(TRUE(),MOD(C3-B3,7)=0,INT((C3-B3)/7)&" weeks ",MOD(C3-B3,7)=1,INT((C3-B3)/7)&" weeks "&MOD(C3-B3,7)&" day",MOD(C3-B3,7)>1,INT((C3-B3)/7)&" weeks "&MOD(C3-B3,7)&" days")

I have named intermediate calculations if they are repeated in the formula, this creates a shorter formula.

y - C3-B3

x - MOD(y,7)

z - INT((y)/7)

q - " weeks "

LET(y,C3-B3,x,MOD(y,7),z,INT((y)/7),q," weeks ",SWITCH(TRUE(),x=0,z&q,x=1,z&q&x&" day",x>1,z&q&x&" days"))

### Dates basic formulas category

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]

If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula. […]

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]

The formula in cell C3 calculates the last date for the given month and year in cell B3. =DATE(YEAR(B3), MONTH(B3)+1, […]

Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]

Excel contains a great function that easily counts the number of workdays between two dates, it even allows you to […]

The image above demonstrates the DATEDIF function calculating the number of complete years between two dates. Column B and column C […]

The image above shows a formula in cell D3 that extracts the latest date in cell range B3:B15. =MAX(B3:B15) The MAX […]

The DATEDIF function in cell E3 allows you to calculate days between two dates.

The image above shows the DATEDIF function calculating the number of complete months between two dates. Column B and column C […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form