When planning or organizing something with a longer duration, it is more useful to show the period in terms of weeks rather than days. The report is then easy to understand and simplify.
In this article, I will show you some simple methods to convert the number of days to weeks in Excel.
Method 1 – Using Simple Division to Convert the Number of Days to Weeks
Every week has seven days. So, dividing the number of days by 7 gives the number of weeks.
The table below shows the project schedule. Column A has the name of the activity, and Column B has the number of days for each activity.
Now, I want to convert the number of days in column B to the number of weeks in Column C.
To convert the number of days to weeks, I can simply subtract the number of days by 7. The logic for this is that every week has 7 days.
So, I am following the below steps.
- Applying the below formula in cell C2 and copy that formula to the below cells.
=B2/7
- Select all the cells to which I applied the formula. So, I am selecting cells C2 to C5.
- Go to the Home tab and click the “Decrease Decimal” icon until Excel removes all the decimal places of the selected cells.
Now, the selected cells show the rounded number of weeks.
In this method, some numbers are rounded up while others are rounded down.
In other words, some calculations only show whole weeks, while others include incomplete weeks as well.
So, when I want consistency in the number of weeks calculation (when I want either the number of weeks completed or the number of weeks, including incomplete weeks), this method is not ideal for me.
But this is a really simple and quick method to convert the number of days to weeks in Excel.
Also read: Convert Days to Months in Excel
Method 2 – Using ROUNDDOWN and ROUNDUP Functions
In the previous method, we could not get the number of weeks completed for all cells.
I can overcome that problem by applying the ROUNDDOWN or ROUNDUP function to the above formula.
The below table shows the project plan. Column A contains the activity name, and Column B contains the number of days for each activity.
Now, I want to convert the number of days in column B to the number of completed weeks in Column C.
I can use the below formula to get the number of completed weeks.
=ROUNDDOWN(B2/7,0)
The syntax of the ROUNDDOWN function is ROUNDDOWN(number, num_digits).
For the first argument, I am entering the formula that divides the number of days by 7 (the number of days per week is 7).
Then, for the last argument, I enter 0 as I don’t want to see any decimal numbers.
If I want to convert the number of days to weeks that include the incomplete week, then I have to use the ROUNDUP function.
=ROUNDUP(B2/7,0)
The syntax of the ROUNDUP function is ROUNDUP(number, num_digits).
The arguments of the ROUNDUP function are similar to the arguments of the ROUNDDOWN function.
For the first argument, I am entering the formula that divides the number of days by 7 (the number of days per week is 7).
Then, for the last argument, I enter 0 as I don’t want to see any decimal numbers. Now I get the number of weeks, including the incompleted week.
Also read: Convert Date To Day Of Week
Method 3 – Using the INT Function
To get the completed number of weeks for the given number of days, I can use the Excel INT function.
This function helps to round down a number to its nearest integer (whole number).
The below table shows the project plan. Column A contains the activity name, and Column B contains the number of days for each activity.
Now, I want to convert the number of days in column B to the number of weeks in Column C.
I can use the below formula to get the number of completed weeks.
=INT(B2/7)
The syntax of the INT function is INT(number). The INT function has only one argument and that is number. For this argument, I am entering the formula that divides the number of days by 7. I am dividing the number of days by 7 because every week has 7 days.
Also read: Convert Date to Serial Number in Excel
Method 4 – Using the QUOTIENT Function
In order to get the completed number of weeks for the given number of days, I can use the Excel QUOTIENT function.
This function helps to get the integer value of the division result.
The table below displays the project schedule. Column A carries the name of the activity, and Column B contains the number of days it will take to complete.
Now, I want to convert the number of days in column B to the number of weeks in Column C.
I can use the below formula to get the number of completed weeks.
=QUOTIENT(B2,7)
The syntax of the QUOTIENT function is QUOTIENT(numerator, denominator).
For the first argument of the function, I have to enter the number that I am going to divide.
In this case, I have to enter the number of days for the first argument. So, I am selecting cell B2 as the numerator. The denominator is the divisor or the number by which another number is divided.
To convert the number of days to weeks, I have to divide the number of days by 7. So, I have to enter 7 as the second argument of the QUOTIENT function.
Using the above QUOTIENT function, I can get the number of completed weeks. Assume I want to know the number of weeks, including the incomplete week. Then I simply add a 1 to the end of the above formula.
=QUOTIENT(B2,7)+1
Also read: Number of Months Between Two Dates in Excel
Method 5 – Finding the Number of Weeks Between Two Dates
All the above methods show how to convert the number of days to weeks in Excel.
Sometimes we need to find the number of weeks between two dates. So, it is important to learn how to calculate the number of weeks between two dates as well.
The below table shows the project plan. Column A contains the activity name, Column B contains the start date of each activity and Column C contains the finish date of each activity.
Now, I want to find the number of weeks between the two dates.
I can use the below formula to get the number of completed weeks.
=ROUNDDOWN((C2-B2)/7,0)
In this example, I don’t have the number of days between two dates.
So, I have to find the number of days by subtracting the start date from the finish date.
Then, to convert the calculated number of days to weeks, I am dividing that by 7 as every week has 7 days.
Next, I am using the ROUNDDOWN function to get the completed number of weeks.
If I want to get the number of weeks, including the incomplete weeks, then I have to use the ROUNDUP function instead of the ROUNDDOWN function.
Now, we have discussed five different methods to convert the number of days to weeks in Excel.
You can modify the formulas based on whether you want the number of completed weeks or not.
Other Excel articles you may also like:
