Weeks Between Two Dates in Excel: Formula Guide

To calculate the number of weeks between two dates in Excel, use the formula =INT((B1-A1)/7) for full weeks, or =(B1-A1)/7 for a decimal value. This article explains the different formulas, how to handle partial weeks, and common pitfalls.

Formula

The core formula is simple because Excel stores dates as serial numbers. Each whole day equals 1. So the difference between two dates in days is just end_date - start_date. To convert that to weeks, divide by 7.

• Decimal weeks: =(end_date - start_date) / 7 — gives a result like 10.57 weeks.
• Whole weeks (floor): =INT((end_date - start_date) / 7) — gives the number of complete 7-day periods.
• Whole weeks (rounded): =ROUND((end_date - start_date) / 7, 0) — rounds to the nearest whole week.

All formulas assume your dates are real Excel dates (not text). The result is a number; you can format it as a number with decimals if needed.

Worked Example

Using the default values from the calculator above: start date 2023-01-01 and end date 2023-03-15.

1. First, find the difference in days: 2023-03-15 - 2023-01-01 = 73 days (Excel does this automatically).
2. Divide by 7: 73 / 7 = 10.428571...
3. Rounded to 2 decimals: 10.43 weeks.

That matches the calculator output. If you used =INT(73/7) you would get 10 full weeks, with 3 days left over.

How Excel Handles Dates

Excel dates are actually numbers. January 1, 1900 is serial number 1, January 2, 1900 is 2, and so on. This means subtracting two date cells gives you the number of days between them, which is the foundation for any week calculation.

One important detail: if your dates include time (e.g., 2023-01-01 14:00), the serial number includes a fraction. In that case, the difference might be 72.5 days, and dividing by 7 gives 10.36 weeks. To ignore time, use =INT(end_date) and =INT(start_date) before subtracting.

Using DATEDIF for Weeks

Excel has a hidden function called DATEDIF that can calculate days, months, or years, but not directly weeks. However, you can combine it with division:

=DATEDIF(start_date, end_date, "d") / 7

This gives the same result as the simple subtraction. The advantage of DATEDIF is that it handles date ordering more gracefully (if start is after end, it returns an error). But for most cases, plain subtraction is simpler and more transparent.

Rounding and Displaying Results

How you round depends on what you need:

• Project planning: Use =ROUNDUP((end-start)/7, 0) to ensure you have enough weeks (ceil).
• Billing or payroll: Use =ROUND((end-start)/7, 2) for a precise decimal.
• Counting full weeks only: Use =INT((end-start)/7).

You can also format the cell to show “weeks� by using a custom number format like 0.00 "weeks".

Common Mistakes and Pitfalls

• Text dates: If your dates are text, subtraction won’t work. Use =DATEVALUE() to convert them first.
• Reversed dates: If start is after end, you get a negative number. Use =ABS((end-start)/7) if you always want a positive result.
• Inclusive vs exclusive: The formula counts the difference, not including the start day. If you want to include both endpoints, add 1 to the day difference before dividing.
• Time components: As mentioned, times can cause fractional days. Use =INT() on both dates to ignore time.
• Week definition: Some people define a week as Monday-to-Sunday. The simple /7 formula doesn’t care about weekdays. If you need calendar weeks, you might need =WEEKNUM() instead.

Alternatives: WEEKNUM and NETWORKDAYS

If you need the number of weeks between two dates based on week numbers (e.g., week 5 to week 12), you can use:

=WEEKNUM(end_date) - WEEKNUM(start_date)

But this counts the difference in week numbers, not the actual number of 7-day periods. For example, from a Saturday to the next Sunday would be 1 week by this method, even though only 8 days have passed.

For business weeks (Monday to Friday), use =NETWORKDAYS(start_date, end_date)/5 to get the number of work weeks. This excludes weekends and optionally holidays.

Frequently Asked Questions

How do I calculate weeks between two dates in Excel including partial weeks?

Use =(end_date - start_date)/7 and format the cell as a number with decimals. This gives you a decimal like 3.43 weeks, which includes partial weeks.

What is the difference between INT and ROUND for weeks?

INT always rounds down to the nearest whole number (floor), so 2.99 becomes 2. ROUND rounds to the nearest whole number, so 2.99 becomes 3. Choose based on whether you need complete weeks or a rounded estimate.

Why does my formula return a date instead of a number?

Excel sometimes formats the cell as a date automatically. Right-click the cell, choose Format Cells, and select “Number� or “General�. The underlying value is correct; it’s just the display.

Can I calculate weeks between dates in Google Sheets the same way?

Yes, Google Sheets uses the same date serial system and the same formulas work: =(B1-A1)/7.

How do I count weeks between two dates excluding weekends?

Use =NETWORKDAYS(start_date, end_date)/5. This counts only Monday through Friday and divides by 5 to get work weeks.

What if my dates are in different cells and I want the result in years and weeks?

You can combine formulas: for years, use =DATEDIF(start_date, end_date, "y"), and for weeks, use =INT((end_date - DATE(YEAR(start_date)+years, MONTH(start_date), DAY(start_date)))/7). But it’s simpler to just show total weeks as a decimal.

Related

• Arc Raiders Tech Loot: How Thermo Lining Works
• Peacock TV Keyboard Shortcuts (Windows & Mac)
• Dudes Best Team Build Roles & Top Dudes Guide

in start | Start date | - | 2023-01-01 | 0
in end | End date | - | 2023-03-15 | 0
out weeks | Weeks between | weeks | (end - start) / 7 | 2
out days | Total days | days | end - start | 0