Author: Oscar Cronquist Article last updated on October 23, 2018

The image above demonstrates a formula that calculates the number of complete weeks between two dates. Column B and column C contains random dates.

Formula in cell D3:

=ROUNDDOWN((C3-B3)/7)

Copy cell D3 and paste to cells below as far as needed.

Explaining formula in cell D3

This formula works fine if the start is later than the end date, however, you get a minus sign before the number.

If you want to remove the minus sign simply use the ABS function to remove it, the formula then becomes:

=ABS(ROUNDDOWN((C3-B3)/7))

Step 1 - Subtract dates

C3-B3

becomes

35067 - 35685 equals 618 days.

Step 2 - Divide with 7

There are seven days in a week so we need to divide the result with 7.

(C3-B3)/7

becomes

618/7 equals 88.28571429.

Step 3 - Round the number down

The ROUNDDOWN function rounds the number down.

ROUNDDOWN((C3-B3)/7)

becomes

ROUNDDOWN(88.28571429) and returns 88.