When working with dates and making project plans in Excel, you may come across a situation where you want to know how many weeks there are between two dates.
While there is no built-in function to do this in Excel, this can easily be done using other simple functions.
In this short article, I will show you how to calculate the number of weeks between two dates in Excel.
Click here to download the example file and follow along
DAYS Function to Get Weeks Between Two Dates
A week always has seven days, so if we can find the total number of days between two given dates, we can easily find the total number of weeks by dividing this value by 7.
Below, I have a data set where I have the start date in column A and the End date in column B, and I want to calculate the total number of weeks between these two dates (note that the dates are in dd-mm-yyyy format).
Here is the formula that will do this:
=DAYS(B2,A2)/7
I entered this formula in cell C2 and then copied it for all the other cells in the column to get the result.
DAYS function subtracts the start date from the end date to give us the total number of days between these two given dates.
This value is then divided by 7 to get the total number of weeks between the two dates.
In case you see dates in column C instead of numbers, click the Home tab, then in the Number group, click on the Format drop-down and select General.
As you must have already noticed, some of the results have decimal values (such as 24.142857). In this case, 24 gives us the total number of completed weeks between these two dates, and the decimal portion gives us the additional days after 24 weeks.
In case you only want to get the number of completed weeks, you can use the below formula:
=INT(DAYS(B2,A2)/7)
This will remove the decimal portion and only give you the integer part of the result.
In case you want to round up the value and count any decimal part as a complete week, you can use the formula below:
=ROUNDUP(DAYS(B2,A2)/7,0)
Note: The result you get is not inclusive of both the start date and the end date. For example, if your project starts on 1st January and ends on 11th January, the DAYS function would return 10. If you want the start date and the end date to be included in the counting of days, you need to add 1 to the result of the DAYS function.
Also read: How to Add Week to Date in Excel?
Subtract Dates to Get Weeks Between Two Dates
Since dates are stored as serial numbers in Excel, you can also do a simple subtraction to get the number of days between two given dates and then calculate the number of weeks from that.
Below, I have the data set where I have the Start date in column A and the End date in column B, and I want to calculate the number of weeks between these two dates (note that the dates are in dd-mm-yyyy format).
Here is the simple subtraction formula to do this:
=(B2-A2)/7
Enter this formula in cell C2 and copy it for all the other cells in the column.
In case you only want to get the number of completed weeks, you can use the below formula:
=INT((B2-A2)/7)
Note: For this method to work, your dates need to be in a format that Excel recognizes as a valid date format and it shouldn’t be in the text format. In some cases, when your date is in the text format, this formula may return a VALUE error.
Also read: Get the Number of Days in a Month in Excel
WEEKNUM Function to Get Weeks Between Two Dates
Another smart way to calculate the number of weeks between two dates is by using the WEEKNUM function.
Below, I have the same data set where I have the Start Date in column A and the End Date in column B, and I want to calculate the number of weeks between these two dates (note that the dates are in dd-mm-yyyy format).
Here is the WEEKNUM formula to do this:
=WEEKNUM(B2-A2)
Enter this formula in cell C2 and copy it for all the other cells in the column.
WEEKNUM Function works by taking the serial number of a date as input and giving us the week number of that date in that year.
However, in this example, we are using B2-A2 as the date serial number, which makes the formula count the total number of weeks from the beginning of the year till the number of days between the start and end date.
Also, note that this will always give you an integer value that represents the total number of weeks between these two dates. It would automatically round up the result, so if there are 16 days, it would give you three weeks.
Click here to download the example file
These are some simple formula methods you can use to quickly calculate the number of weeks between two given dates.
I hope you found this article useful. If you know of any other method that can be used to do this, please share with us in the comments section.
Other Excel articles you may also like:
