Excel Formula for Weeks Between Two Dates: Full Weeks

To calculate weeks between two dates in Excel, subtract the start date from the end date and divide by 7. If the start date is in A2 and the end date is in B2, enter =(B2-A2)/7 to return elapsed weeks, including a decimal for any partial week.

What the basic formula calculates

Excel stores dates as serial numbers, so subtracting one valid date from another gives the number of days between them. Dividing that difference by 7 converts days to weeks. For example, if A2 contains April 1, 2025, and B2 contains May 20, 2025, the difference is 49 days, or exactly 7 weeks. With those dates in A2 and B2, the formula =(B2-A2)/7 returns 7.

The result can be a decimal. If the dates are 17 days apart, the elapsed time is 17 divided by 7, or about 2.43 weeks. That is usually the most informative answer when you want a duration rather than a count of whole weeks. You can fill the formula down a column to calculate durations for many pairs of dates, as long as each row uses the same start-date and end-date layout.

This calculation treats a week as seven consecutive 24-hour days. It does not mean “the number of calendar weeks touched by the dates,” nor does it count only weekdays. Those are different questions and can produce different results.

Set up dates as real Excel dates

Before relying on the result, make sure the cells contain Excel date values rather than text that merely looks like a date. You can usually enter dates in a familiar format, such as 4/1/2025, and apply a date format using Home > Number Format. The displayed appearance may vary by regional settings, but changing the format does not change the underlying date value.

On Microsoft 365 for Windows, Mac, and Excel for the web, the subtraction formula works the same way. In some regional configurations Excel expects semicolons rather than commas between function arguments; the basic subtraction formula has no argument separators, so that regional difference does not affect it. If a result appears as a date instead of a number, change the result cell’s format to General or Number. A date-formatted cell can display a numeric week result confusingly even when the arithmetic is correct.

Put the earlier date in A2 and the later date in B2 when you want a positive duration.
Check that both cells are recognized as dates, not text.
Format the result as General or Number to see decimal weeks.
Use consistent date conventions in the source data, particularly when day and month could be ambiguous.

Show only completed whole weeks

Sometimes the question is not how many weeks have elapsed as a decimal, but how many complete seven-day periods have passed. With the start date in A2 and the end date in B2, use =QUOTIENT(B2-A2,7). For dates 17 days apart, this returns 2, because two full seven-day periods have elapsed and three days remain. For dates exactly 21 days apart, it returns 3.

For ordinary date-only values in chronological order, =INT((B2-A2)/7) also returns the number of completed weeks. QUOTIENT expresses the “whole groups of seven” intent directly and truncates the quotient toward zero. That distinction matters if the dates are reversed: a negative difference produces a negative result, and truncating toward zero differs from rounding down to the next lower integer. If your worksheet permits reversed date order, decide whether a negative duration is appropriate or whether the dates should be swapped.

A full-week result discards the leftover days, so it is less precise than the decimal calculation. Use it for rules such as “how many complete weeks have passed?” rather than reporting a duration where partial weeks matter.

Round or display partial weeks

If decimal weeks are useful but you want a cleaner display, round the result to a chosen number of decimal places. With A2 as the start date and B2 as the end date, =ROUND((B2-A2)/7,2) rounds to two decimal places. A 17-day interval then displays as 2.43 weeks. Rounding changes the reported precision; it does not change the actual elapsed time.

Other rounding choices answer different questions. With the same A2 and B2 date layout, =ROUNDUP((B2-A2)/7,0) rounds a positive duration up to the next whole week whenever any fraction remains. For a 17-day span, it returns 3. This can be useful when a policy charges or schedules by any portion of a week, but it should not be described as the number of completed weeks. With A2 as the start and B2 as the end, =ROUNDDOWN((B2-A2)/7,0) removes the fractional part for a positive interval; for 17 days, it returns 2. For negative intervals, rounding functions have sign-specific behavior, so confirm that it matches your intended rule.

To show a fixed number of decimal places without changing the underlying calculation, you can also format the result cell with the number-format controls on the Home tab. A formula can retain its full precision while the cell displays, for example, two decimal places. That is often preferable when later calculations will use the result.

Calendar weeks are not the same as seven-day durations

A common source of confusion is crossing a week boundary. Friday to Monday is only three elapsed days, or about 0.43 weeks, but those dates fall in different Monday-start calendar weeks. The basic subtraction formula measures duration and will not return 1 simply because a new calendar week began.

If you need to count Monday-start boundaries crossed, use a boundary-based calculation instead. With the earlier date in A2 and the later date in B2, =INT((B2-WEEKDAY(B2,2)+1)/7)-INT((A2-WEEKDAY(A2,2)+1)/7) counts the change in Monday-based week boundaries. For Friday, May 2, 2025, in A2 and Monday, May 5, 2025, in B2, it returns 1, although the elapsed duration is only three days. The formula uses WEEKDAY with return type 2, where Monday is day 1.

Calendar-week calculations depend on what your organization means by the start of a week and, in some cases, which calendar-year week-numbering system it follows. A week-number difference can also look wrong near New Year because the week number resets or the dates may belong to different week-years. For routine elapsed time, subtracting dates and dividing by 7 is clearer. Use a boundary or week-number method only when the requirement explicitly concerns calendar weeks.

Dates that include times

Excel date-time values include a fractional day for the time of day. Therefore, subtracting two date-time values and dividing by 7 measures exact elapsed periods, not just the difference between the dates shown on screen. If A2 is January 1 at 9:00 a.m. and B2 is January 8 at 9:00 a.m., the interval is exactly 7 days, so the basic formula returns 1. If B2 is January 8 at 8:00 a.m., the interval is one hour short of a full week, even though the displayed dates are seven calendar dates apart.

If the times should be ignored and only whole dates matter, remove the time portions before calculating. With date-time values in A2 and B2, =(INT(B2)-INT(A2))/7 calculates the difference between their calendar dates as a decimal number of weeks. This is appropriate only when time of day is irrelevant to the question. It can produce a different answer from the exact elapsed-time calculation, so choose deliberately.

Handle blanks and reversed dates

A blank cell can behave like zero in arithmetic, which may create a large and misleading result instead of an empty output. If A2 contains the start date and B2 contains the end date, use =IF(OR(A2="",B2=""),"",(B2-A2)/7) to leave the result blank until both date cells have entries. This formula assumes dates are in A2 and B2; it does not validate whether the entered values are genuine dates or whether the end date follows the start date.

If your data may put the dates in either order and you want the duration to be positive regardless of order, use =ABS(B2-A2)/7 with the two dates in A2 and B2. ABS removes the sign, so it is suitable for an unsigned duration, but it also hides which date came first. If direction matters—for example, to identify overdue or future intervals—keep the signed result and handle reversed dates as a data issue or a meaningful negative duration.

If Excel displays a formula error, check for text dates, extra spaces, missing values, or dates entered under an unexpected regional convention. Re-entering a value as a recognized date or correcting its source is better than trying to disguise invalid input with a more complicated formula.

Choosing the right calculation

Before selecting a formula, decide what “weeks between” means in the task. These common interpretations are not interchangeable:

Elapsed weeks, including a fraction: subtract the dates and divide by 7.
Completed seven-day periods: use QUOTIENT with the date difference and 7.
A rounded billing or planning unit: use an appropriate rounding rule, and state whether partial weeks round up or down.
Calendar week boundaries crossed: compare the week-start boundaries, using the correct week-start day for the business rule.
Working time: calculate business days separately; a calendar week is not five working days in every schedule, and holidays may matter.

Excel’s NETWORKDAYS function can count working days between dates, optionally excluding a supplied holiday range, but it answers a different question from elapsed calendar weeks. If you divide business days by five, you are adopting a five-workday-week convention; that may be useful for planning, but it is not the same as the actual number of calendar weeks. Document the convention when the result will be used by other people.

Frequently Asked Questions

What is the simplest Excel formula for weeks between two dates?

With the start date in A2 and the end date in B2, use =(B2-A2)/7. It returns elapsed weeks as a decimal, so partial weeks are included.

How do I get only the number of full weeks?

With dates in A2 and B2, use =QUOTIENT(B2-A2,7). For a positive interval, it counts complete seven-day periods and leaves any remaining days out of the result.

Why does Excel show a decimal instead of a whole number?

The formula divides the day difference by 7, so a span that is not an exact multiple of seven days has a fractional result. Use a rounding formula if the task calls for a rounded figure, or use QUOTIENT if you want only completed weeks.

Does the formula count weekends?

Yes. Subtracting dates measures calendar days, including Saturdays and Sundays. For working-day calculations, use a business-day method and account for the applicable work schedule and holidays.

How can I count weeks if the end date is earlier?

The basic formula returns a negative value when B2 is earlier than A2. Use ABS around the difference if you want only a positive duration; keep the negative sign if direction carries meaning in your worksheet.

Why does a three-day interval sometimes count as one week?

It does not count as one elapsed week. It may cross a calendar-week boundary—for example, Friday to Monday—so a calendar-boundary calculation can return 1 while the elapsed-time formula returns three-sevenths of a week.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.