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. Cell range B3:B14 contains the start dates and cell range C3:C14 contains the end 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
This formula returns the total number of weeks and days between a given start and end date.
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
This formula works only in Excel 365, it calculates weeks and days between a given start and end date. It is also dynamic meaning if the result is a whole week the number of days is left out from the output.
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 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 " 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 […]
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.
