Convert Number of Days to Weeks in Excel

Sometimes we may want to convert the number of days into weeks in Excel summary reports.

Convert Number of Days to Weeks in Excel

In this tutorial we give four examples to show how to convert a number of days to weeks in Excel using the following dataset:

We will convert the days to a number of weeks with decimals and then convert the days to the number of weeks with days using Excel functions.

Use the ROUNDOWN, INT, MOD, and IF functions to convert days to weeks

We give four examples to explain how the ROUNDOWN, INT, MOD, and IF functions can be used in Excel to convert a number of days into weeks.

Example 1: ROUNDOWN and IF functions to convert days to weeks with decimals

Sometimes we may want to convert the number of days to weeks and retain the decimals.

To convert the number of days to a number of weeks with decimals we use the following steps:

  1. Select cell B2 and type in the following formula:
=ROUNDDOWN(A2/7,1) & IF(ROUNDDOWN(A2/7,1)=1, " week", " weeks")
Table

Description automatically generated
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:
Table

Description automatically generated

Explanation of the formula

=ROUNDDOWN(A2/7,1) & IF(ROUNDDOWN(A2/7,1)=1, " week", " weeks")

The formula uses the ROUNDDOWN and IF functions.

The ROUNDDOWN function rounds a number down towards zero. Its syntax is ROUNDDOWN(number, num_digits). The number argument is required, and it is any real number that we want to be rounded down. The num_digits argument is required, and it is the number of digits to which we want to round the number.

If num_digits is 0 (zero), the number is rounded down to the nearest integer. If it is greater than 0 (zero), the number is rounded down to the specified number of decimal places. The number is rounded down to the left of the decimal point if it is negative.

The IF function checks whether a condition is met, and returns one value if True, and another value if False. Its syntax is IF(logical_test, [value_if_true], [value_if_false]).

ROUNDDOWN(A2/7,1). In this first part of the formula, the value in cell A2 is divided by 7 and the result is rounded down to one decimal place.

IF(ROUNDDOWN(A2/7,1)=1, ” week”, ” weeks”). In this second part of the formula, if the value returned by the ROUNDDOWN function is equal to 1, the IF function returns “week” otherwise it returns “weeks.”

The ampersand (&) operator joins the result of the first part of the formula to the second part of the formula to display the number of weeks with decimals.

Example 2: INT and IF functions to convert a number of days to weeks and days

Sometimes we want to convert the number of days to weeks and show the balance of days that did not add up to a week.

To convert the number of days to a number of weeks with days we use the following steps:

  1. Select cell C2 and type in the following formula:
=INT(A2/7) & IF(INT(A2/7)=1, " week", " weeks") & " and " & (A2-INT(A2/7)*7) & IF((A2-INT(A2/7)*7)=1, " day", " days")
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column.
Table

Description automatically generated

Explanation of the formula

=INT(A2/7) & IF(INT(A2/7)=1, " week", " weeks") & " and " & (A2-INT(A2/7)*7) & IF((A2-INT(A2/7)*7)=1, " day", " days")

The formula applies the INT function and the IF function that was explained previously.

The INT function rounds a number to the nearest integer. Its syntax is INT(number). The number argument is required; it is any real number we want to round down to an integer.

INT(A2/7) & IF(INT(A2/7)=1, " week", " weeks")

In this first part of the formula, the value in cell A2 is divided by 7 and the INT function rounds the result to the nearest integer. The returned integer is later passed to the IF function. If the integer is equal to 1 the IF function returns “week” otherwise it returns “weeks.” The values returned by the INT and IF functions are then joined by the ampersand (&) operator.

(A2-INT(A2/7)*7) & IF((A2-INT(A2/7)*7)=1, " day", " days")

In this second part of the formula, the value in cell A2 is divided by 7 and the result is rounded to an integer. The integer value is then multiplied by 7 to convert it to the number of days. The product is subtracted from the number in cell A2 resulting in the number of days that could not add up to a week.

The result is later passed to the IF function. If the result is equal to 1, the IF function returns “day” otherwise it returns “days.” The number of days is then joined to the value returned by the IF function by the ampersand (&) operator.

The result of the first part of the formula is then joined to the result of the second part of the formula using the ampersand (&) operators to display the number of weeks and number of days.

Example 3: ROUNDDOWN and IF function to convert days to weeks and days

In Example 1, we used the ROUNDDOWN and IF functions to convert the number of days to weeks and decimals. In this example, we use the functions to convert the number of days to weeks and days.

We use the following subset of our dataset:

Table

Description automatically generated

We use the following steps:

  1. Select cell B2 and type in the following formula:
=ROUNDDOWN(A2/7,0) &IF(ROUNDDOWN(A2/7,0)=1, " week", " weeks") & " and " & (A2-ROUNDDOWN(A2/7,0)*7) & IF((A2-ROUNDDOWN(A2/7,0)*7)=1, " day", " days")
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column.
Table, Excel

Description automatically generated

Explanation of the formula

ROUNDDOWN(A2/7,0) &IF(ROUNDDOWN(A2/7,0)=1, " week", " weeks")

In this first part of the formula, the value in cell A2 is divided by 7. The ROUNDDOWN function rounds the result to an integer with 0 (zero) decimal places.

The integer value is passed to the IF function in the next part of the formula. If the integer value is equal to 1 the IF function returns “week” otherwise it returns “weeks.” The values returned by the ROUNDDOWN and IF functions are then joined by the ampersand (&) operator.

(A2-ROUNDDOWN(A2/7,0)*7) & IF((A2-ROUNDDOWN(A2/7,0)*7)=1, " day", " days")

In this second part of the formula, the integer value returned by the ROUNDDOWN function is multiplied by 7 to convert it to a number of days. This number of days is subtracted from the value in cell A2.

The result is the balance number of days that could not add up to a week. In the next part of the formula, the number of days that could not add to a week is passed to the IF function. If the number is equal to 1 the IF function returns “day” otherwise it returns “days.”

The result of the first part of the formula is finally joined to the result of the second part of the formula using the ampersand (&) operators to display the number of weeks and days.

Example 4: INT, IF, and MOD functions to convert days to weeks and days

In this example we use INT, IF, and MOD functions to convert a number of days to weeks and days in Excel using the following subset of our dataset:

We use the following steps:

  1. Select cell B2 and type in the formula:
=INT(A2/7) & IF(INT(A2/7)=1, " week", " weeks") & " and " & MOD(A2,7) & IF((MOD(A2,7))=1, " day", " days")
Graphical user interface, text, application

Description automatically generated
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column.
Graphical user interface, application, table, Excel

Description automatically generated

Explanation of the formula

INT(A2/7) & IF(INT(A2/7)=1, " week", " weeks")

In this first part of the formula, the value in cell A2 is divided by 7 and the INT function rounds the result to an integer. The integer is then passed to the IF function. If the integer is equal to 1, the IF function returns “week ” otherwise it returns “weeks. “

The MOD function is used In the second part of the formula. The MOD function returns the remainder after a number is divided by a divisor. Its syntax is MOD(number, divisor). The number argument is required and it is the number for which we want to find the remainder. The divisor argument is required, and it is the number by which we want to divide the number.

MOD(A2,7) & IF((MOD(A2,7))=1, " day", " days")

The MOD function returns the remainder when the value in cell A2 is divided by 7. This result is passed to the IF function. If the result is equal to 1 the IF function returns “day” otherwise it returns “days.” The values returned by the MOD and IF functions are joined or concatenated by the ampersand (&) operator.

The result of the first part of the formula is finally joined to the result of the second part of the formula using the ampersand (&) operators to display the number of weeks and days.

Conclusion

Sometimes we may want to convert the number of days in our Excel worksheets into weeks.

In this tutorial, we have given four examples to explain how we can use the INT, IF, MOD, and ROUNDOWN functions to convert the number of days to weeks in Excel.