Part of Excel norms is working with numbers and dates because that is Excel's forte. Where dates make the central part of the data, you may be working on them to add days, months, or years to them or to find the difference between them in days, months, and years.
We'll walk you through counting the number of weeks between two dates today. By the end of this tutorial, you'll learn counting whole weeks, nearest whole weeks, and weeks and days between dates, tied up together with a couple of Excel tricks.
Let's get counting!
Table of Contents
Method #1 – Counting Weeks Between Dates
Let's learn first how we can calculate the number of weeks between two dates in Excel. We can then take things up a notch and calculate weeks and days between two dates.
Counting Whole Weeks
Let's start with plain vanilla and get the add-ins to come in later. The first thing we are going to do is compute the difference in weeks between the dates with a bit of smoothing over using the INT function. The INT function rounds a number down to the nearest integer. Look at the formula below used on an example and we'll show you why we would like to have the INT function to count weeks between dates:
=INT((D3-C3)/7)
We take the end date (in cell D3) first and subtract the start date (C3) from it. D3-C3 gives us the answer in days i.e. 14. D3-C3 is then divided by 7 so we get the answer in weeks instead and so, we arrive at 2.
This first instance was straightforward but if we look at C4 and D4, there is a difference of 20 days, and divided by 7, it gives 2.85714 and beyond. For such instances, we have the INT function ready in the formula to round the number down to the nearest whole number. Therefore, we get 2 as the result in the second instance even though it is closer to 3. This is because our objective is to count the complete weeks that have lapsed between the two dates.
The numerically logical way of calculating the difference between dates is to subtract the earlier date from the later date because the other way around would incur a minus sign. That can be tackled with the ABS function, which ignores the sign of a number. Now if you were subtracting the later date from the earlier date, this would be the formula to use:
=ABS(ROUNDDOWN((C3-D3)/7,0))
As you can see, we haven't just added the ABS function, but we've also replaced the INT function with the ROUNDDOWN function. ROUNDDOWN will round the outcome of C3-D3)/7 down and also requires the number of decimal places you want. Since we want an integer, ROUNDDOWN's second argument is 0. The end date is being subtracted. Therefore, the integer will carry a minus sign. The ABS function returns the value without its sign.
That is what is going on in the formula. Let us tell you why you can't extend the INT formula while dealing with minus signs. INT rounds the number down to the nearest integer so if we are at -2.85, INT will round it down to -3. The ROUNDDOWN function rounds the number down toward 0. Hence, negative figures won't be rounded down to the greater negative figure but would be rounded off close to 0 i.e. -2.
Nearest Whole Weeks
Previously we were counting the whole weeks between two dates. Now we'll show you how to calculate the difference that is closer to the nearest whole number of weeks. Just a slight change in the formula from above should do it. Note the following formula:
=ROUND((D3-C3)/7,0)
Subtracting the dates, dividing them by 7, and maintaining that we don't want any decimal places, the ROUND function rounds the number up or down to the closest digit and the mentioned number of digits. Looking at the results we can note in the second instance that 2.85 has been rounded up to 3 instead of 2.
Adding Text
How many times have you answered purely numerically in your math classes, to have the teacher ask you "5 what? 5 donkeys, 5 pizzas, 5 shoes?". Where the unit of measurement is embarrassingly important, here's how to add the relevant text to the outcome. Use the following formula:
=INT((D3-C3)/7)&" weeks"
We've taken our first and plain vanilla formula of the day and extended it with the & operator and the text " weeks" enclosed in double quotes. Be sure to add the space before the text so you don't get 2weeks in the result.
Happy teachers, happy days.
Note: The results of the formula have snapped from right alignment to left as they have become text values after adding text. Avoid adding text in the same cell if you want to further use the resulting value in calculations and number formulas.
Method #2 – Counting Weeks and Days Between Dates
Building up the formula, we're not just going to aim to get the difference between the dates in weeks now but in weeks and days. For the "days" element, the MOD function will calculate the remaining days. The MOD function returns the remainder when two numbers are divided. This formula will return whole weeks and the remaining days:
=INT((D3-C3)/7)&" weeks "&MOD(D3-C3,7)&" days"
We'll carry forward the last formula where we added text to the full weeks that passed between the dates in C3 and D3. Now to add the days, the & operator has concatenated the result of the MOD function. MOD subtracts C3 from D3 and the divisor is provided as 7; exactly like we have been doing with the INT formula i.e. D3-C3)/7. This expression gives us the whole weeks and the MOD function will give us the remainder of this expression, that is the remainder after the whole weeks.
C3 and D3 are exactly two weeks apart so we have 0 days in the first instance; " days" added as text after the MOD function with the & operator. Mind the right use of the space character in the text strings added in the formula. You may choose to add punctuation marks too such as the comma.
In the second instance, the gap between the dates is of 20 days the INT function returns 2 and the MOD function returns 6. That makes 2 weeks 6 days: spot on!
Adjustment for 0 Weeks
Just now you saw that C3 and D3 in our case example are giving us the difference of 2 weeks and 0 days, 0 days looking a bit odd if not a lot. Now with the plain vanilla going fully topped and loaded, we're getting the last add-ons to the formula with the reuse of the MOD function and addition of the IF function. This formula should help you eliminate seeing 0 days in the result:
=INT((D3-C3)/7)&" weeks "&IF(MOD(D3-C3,7)=0,"",", "&MOD(D3-C3,7)&" days")
Advancing the formula from the previous section, we have added the IF function in the middle. The added bit reads that if the remainder of D3-C3 divided by 7 equals 0, then return an empty string (denoted by "").
So the entire formula goes – number of weeks (computed by D3-C3/7), rounded down with the INT function, and suffixed with the text " weeks ". Then, if the remainder of the weeks calculation is 0, return empty text otherwise return the remainder of the weeks calculation and add " days" in text at the end.
Now you can see from the formula applied to the dataset; the number of weeks is only proceeded by the number of days if the latter doesn't equal 0.
Method #3 – User Defined Function for Counting Weeks
We're making our own formulas all the time so we're going for the anomalous today; making our own function. Use VBA to create the function and apply it effortlessly. The function is similar to the DATEDIF function and you can find it in the steps below along with instructions to create the function:
- Go to the Developer tab > Code group > Visual Basic icon or press the Alt + F11 keys to launch the Visual Basic
- Click on the Insert tab > Module option to open a Module
- Copy the code below and paste it in the Module
'Created by ExcelTrick.com
Function WeekDiff(date1 As Date, date2 As Date) As Long
WeekDiff = DateDiff("ww", date1, date2, vbMonday)
End FunctionThe syntax of the function is given in the first line of the code, date1 being the earlier date and date2 being the later date. In the second line, the third argument is set by us as vbMonday which will count Monday as the first day of the week.
By leaving the third argument out, the function will consider Sunday as the first weekday by default but if you want to change that as we did, when you enter the comma after date2 in line 2, an IntelliSense list will appear with a bubble comment regarding the syntax of the DateDiff function:- Select your favored option by double-clicking it:
- Click on the Run button in the toolbar to run the code or press the F5
- You will see a Macros dialog box appear when you run the code. Enter the name of the function (Weekdiff in this case) in the given field and select the Create button.
- Close the Visual Basic editor now that the function has been created.
- Back to the worksheet, it looks like nothing has changed but when you head to the target cell to enter the formula, type the name of the created function and you will see a case-sensitive entry of the function in Excel's IntelliSense.
- Select the function and enter the formula with the earlier date and then the later date.
And you have the weeks between the dates with a created function! The number of weeks will lean towards the nearest whole figure.
Once you make good acquaintances with Excel, you'll start finding out how little you must do on your own, which is always a good thing – especially in the Excel-verse. We hope you find the methods, tips, and tricks helpful for your task of counting weeks between dates. Keep adding to the plain vanilla as per your requirement. We'll rope you in later with more of all that Excel jazz!
