How to calculate weeks between two dates in Excel

Weeks with a decimal: (end - start) / 7

Column B holds each job's start date and column C its end date. Subtracting two real dates returns the number of days between them, because each date is stored as a whole number of days. Divide that by 7 and you have weeks.

If the result shows as a date like 1/6/1900, the cell picked up a date format from the cells you referenced. Change it to General or Number (Home > Number Format). The value was right all along.

Full weeks only with INT

For "how many complete weeks has this job run", drop the decimal with INT. It always rounds down, so 13.57 becomes 13, which is what people mean by full weeks.

Excel's DATEDIF function has no week unit (its units are "y", "m", "d", "md", "ym" and "yd"), so =DATEDIF(B3,C3,"d")/7 is the same as plain subtraction. There is no need for it here.

"13 weeks 4 days" with INT and MOD

MOD(days,7) returns what is left after taking out the whole weeks. Join both parts with & to get a label that clients and managers read more easily than 13.57.

For a label that never says "1 days", add IF tests for singular: =INT((C4-B4)/7)&IF(INT((C4-B4)/7)=1," week "," weeks ")&MOD(C4-B4,7)&IF(MOD(C4-B4,7)=1," day"," days") returns 3 weeks 1 day for the bathroom job. Keep the number version in another column if you need to sum or sort, because the label is text.

Round up to billable weeks

If you charge or schedule by the week, any part of a week counts as a whole one. ROUNDUP with 0 decimal places rounds every fraction up. The bathroom ran 22 days, which is 3.14 weeks, so it bills as 4.

Be careful with short jobs. The garden wall took 2 days and still rounds up to 1 week. That is usually what a weekly rental or retainer means, but check it matches your contract before you send invoices from it.

Counting both the first and last day

End minus start does not count the start day. The kitchen refit ran Sep 7 to Oct 19: subtraction says 42 days, exactly 6 weeks. If the crew was on site on both of those days, they worked 43 calendar days, which is 6 weeks and 1 day.

Decide which you mean and add 1 when both ends count. The same rule applies to NETWORKDAYS, which already counts both ends: =NETWORKDAYS(B3,C3) returns 70 working days for the loft, which is 14 five-day working weeks, while the calendar calculation said 13.57.

Calendar weeks touched (Monday to Sunday)

Timesheets and rotas often need "how many separate weeks does this job appear in", not the length. The loft starts on a Monday and ends on a Friday 13 weeks and 4 days later, so it shows up on 14 weekly timesheets.

WEEKDAY(date,2) numbers Monday as 1 and Sunday as 7, so date-WEEKDAY(date,2) jumps back to the Sunday before. Do that to both dates, divide the gap by 7 and add 1.

If you need ISO week numbers (week 1 is the week with the year's first Thursday), Excel has ISOWEEKNUM(date) and WEEKNUM(date,21). Subtracting week numbers breaks across New Year, when week 52 is followed by week 1, so use the formula below for counts.

When one date is text

The driveway end date in C6 was pasted from a UK subcontractor's sheet as 16/11/2026. On a US-English system there is no month 16, so Excel keeps it as text and the subtraction fails. A text date that looks like a valid US date can be worse: Excel may quietly read 05/11/2026 as May 11.

Check with =ISNUMBER(C6), which returns FALSE for text. Convert the column with Data > Text to Columns and choose DMY on step 3, or see the guide on converting text to dates.