To count the weeks between two dates, subtract them and divide by seven: =(B1-A1)/7. From 01-Jan-2023 to 04-Jul-2023 that returns 25.86 weeks. Which formula you want depends on how you need the part-week handled.
For completed weeks only, wrap it in TRUNC: =TRUNC((B1-A1)/7) returns 25 and simply drops the remainder. To count a partial week as a full one, use =ROUNDUP((B1-A1)/7,0), which gives 26 — the right choice for billing periods or project scheduling.
And to show weeks and leftover days together, use =TRUNC((B1-A1)/7)&" weeks, "&MOD(B1-A1,7)&" days". Two things to know.
If the result appears as a date rather than a number, the cell has inherited a date format — press Ctrl+1 and set it to Number. And subtracting an earlier date from a later one gives a negative result rather than an error, so wrap it in ABS if your data might have the dates the wrong way round.
In Excel, you can use a formula to calculate the number of weeks between two dates. In this tutorial, we will learn to write this formula.
Here, in this example, we have two dates: the starting date is 01-Jan-2023, and the ending date is 04-Jul-2023.
Formula to Get Weeks Between Two Dates in Excel
- Enter the TRUNC function in a cell.
- Subtract the starting date from the ending date.
- Divide it by 7 as there are 7 days in a week.
- Close the function and hit Enter to get the result.
=TRUNC((B1-A1)/7)
The moment you hit Enter, it returns the count of complete weeks between the specified dates (26 for this example).
The way this formula works is simple: you subtract the starting date from the ending date to get the total number of days between the dates.
Then, it divides that by 7 to get the number of weeks for those days. In the end, when you use the TRUNC function, it only returns the count of the complete weeks, ignoring the decimals.
But if you want to get the exact count of weeks, you can skip using the TRUNC function and use a simple formula like the following:
=(B1-A1)/7
And if you want to count an incomplete week as a complete week, you can use the ROUNDUP function.
In ROUNDUP, you can use the same formula in the number argument and 0 in num_digits. For this example, it returns 27.
=ROUNDUP((B1-A1)/7,0)
Get Weeks and Remaining Days
Sometimes you need the result as weeks plus the days left over, like “26 weeks 2 days”. INT gives you the complete weeks, and MOD gives you the days that remain after them:
=INT((B1-A1)/7)&" weeks "&MOD(B1-A1,7)&" days"
When the End Date Is Before the Start Date
If the dates are the other way round, the formula returns a negative number of weeks (-26 here). If you only need the count of weeks, whichever date comes first, wrap the subtraction in ABS:
=TRUNC(ABS(B1-A1)/7)
Use TRUNC rather than INT here. With a negative number, INT rounds down to -27, while TRUNC just drops the decimals and gives -26.
Count Working Weeks Between Two Dates
If you work five days a week, count the working days with NETWORKDAYS (it skips Saturdays and Sundays) and divide them by 5. You can also add a range of holidays as the third argument of NETWORKDAYS.
=NETWORKDAYS(A1,B1)/5
For this example, it returns 26.4. Note that NETWORKDAYS counts both the start and the end date.
Frequently Asked Questions
Why does my formula return a date instead of a number?
Excel copied the date format from the cells in the formula. Change the format of the result cell to General or Number (Home ➜ Number Format).
Is there a weeks option in DATEDIF?
No. DATEDIF has units for years, months and days, but not for weeks. Use DATEDIF with “d” and divide by 7, or simply subtract the dates as shown above.
What if my dates are stored as text?
Then the subtraction can return the #VALUE! error. Convert the text to real dates first (for example, with DATEVALUE or Data ➜ Text to Columns) and then use the formula.
nice