How to Calculate Weeks Between Dates in Excel

To calculate the weeks between dates in Excel, subtract the start date from the end date and divide by 7: =(B2-A2)/7. Excel stores dates as day numbers, so the subtraction gives days, and dividing by 7 turns them into weeks. This guide shows how to get decimal weeks, full weeks, weeks plus days, rounded-up weeks and working weeks, all on the same small project list.

How to calculate weeks between two dates#

Subtract the earlier date from the later date, then divide by 7. Excel counts each date as a whole number of days, so B2-A2 is the number of days between them.

All the examples use this table:

In D2, type:

=(C2-B2)/7

The Website project is 108 days long, so the result is 15.43 weeks (rounded for display). The brackets matter. Without them, Excel divides B2 by 7 first, because division comes before subtraction, and you get a meaningless number.

If the result shows as a date, the cell picked up a date format. Change it to General or Number on the Home tab. The guide to counting days between dates covers the day count itself in more detail.

Full weeks only: INT or ROUNDDOWN#

Use INT to keep only the complete weeks and drop the part week:

=INT((C2-B2)/7)

For the Website project, 15.43 becomes 15. INT removes the decimal part of a positive number. =ROUNDDOWN((C2-B2)/7,0) gives the same answer for positive results. They only differ for negative results: INT(-15.43) is -16, while ROUNDDOWN gives -15.

The Training project crosses a new year, and the formula doesn't care: date subtraction works across months and years.

Round weeks up or to one decimal place#

Rounding depends on the question you are answering. Pick the function that matches it:

ROUND changes the stored value, not just the display. That matters if you add the weeks up later. The ROUND function guide explains the second argument: 1 keeps one decimal place and 0 rounds to a whole number.

Count weeks including the start and end dates#

Plain subtraction doesn't count the start day. From Monday 8 January to Monday 15 January is 7 days, so =(C2-B2)/7 returns 1 week. That is usually what you want for durations, ages and deadlines.

Sometimes both ends should count, such as a course that runs from the first class day to the last class day inclusive. Then add 1 to the day count before dividing:

=(C2-B2+1)/7

For the Audit project (8 January to 19 February), this gives 43 days, or 6.14 weeks, instead of exactly 6. Decide which rule your report uses, write it in a note next to the column, and use the same rule in every row. Mixing the two is a common reason why two people get different week counts from the same dates.

Show weeks and days together#

To show the result as "15 weeks, 3 days", use INT for the weeks and MOD for the leftover days. MOD returns the remainder after dividing, so MOD(108,7) is 3.

=INT((C2-B2)/7)&" weeks, "&MOD(C2-B2,7)&" days"

The & joins the numbers and the words into one piece of text. For the Training project it shows 11 weeks, 1 days. To fix the grammar for 1, you could add an IF, but most reports accept the short form. The result is text, so you can't do sums with it. Keep a numeric weeks column next to it if you need totals. The guide to MOD and INT explains both functions.

Does DATEDIF calculate weeks?#

No. DATEDIF has units for years, months and days ("y", "m", "d", "md", "ym" and "yd"), but there is no "w" unit for weeks. Divide its day count by 7 instead:

=DATEDIF(B2,C2,"d")/7

This returns the same 15.43 as the subtraction. DATEDIF needs the start date first and returns #NUM! if the start is later than the end, which can be a useful warning. DATEDIF is a hidden function: Excel doesn't suggest it as you type, but it works in every version. The DATEDIF function guide lists every unit, and the article on months between dates shows where DATEDIF really shines.

Count work weeks with NETWORKDAYS#

NETWORKDAYS counts the weekdays (Monday to Friday) between two dates, including both ends. Divide by 5 to get working weeks:

=NETWORKDAYS(B2,C2)/5

The Website project has 79 working days, so this gives 15.8 work weeks. Add a range of holiday dates as the third argument to skip public holidays: =NETWORKDAYS(B2,C2,F2:F10)/5. For a version that counts hours, see working hours between dates.

Weeks between a date and today#

Use TODAY() as the end date to count weeks up to now:

=INT((TODAY()-B2)/7)

The result grows by one each week, because TODAY() updates whenever the file recalculates. This is handy for "weeks since hire" or "weeks on the waiting list". For years and months of service instead, see how to calculate age and tenure, and for fractions of a year, the YEARFRAC function.

A quick check for any of these formulas: if the answer is negative, the dates are the wrong way round. Put the later date first in the subtraction.

Practise it#

Every week formula starts with a correct day count. Practise days between dates with DAYS and DATEDIF in a live spreadsheet, then practise ROUND, ROUNDUP and ROUNDDOWN to turn days into clean week counts. Each formula is checked the moment you press Enter. Or start with the full-weeks exercise on this page.