If you have the number of days and wonder how many weeks that equal to, you can use the following formula:
=INT(A2/7)&" weeks" & " and " & (A2-INT(A2/7)*7) & " days"
Where A2 is the cell with the number of days.
- INT(A2/7) returns the number of weeks (can also use the ROUNDDOWN function);
- A2-INT(A2/7)*7 returns the number of days that are not enough for a week (i.e., less than 7 days).
When the number of weeks or days returns 1, the above formula returns 1 weeks (or 1 days) and that is grammatically not correct. Please use the IF Function to change from plural to singular.
=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")
In case you need to keep the decimals, like 13.1 weeks, please use the formula below.
=ROUNDDOWN(A2/7,1) & IF(ROUNDDOWN(A2/7,1)=1, " week", " weeks")
In this formula, we keep 1 decimal, but you can change the number 1 in the ROUNDDOWN function to any decimals, e.g. 2 in the following formula.
=ROUNDDOWN(A2/7,2) & IF(ROUNDDOWN(A2/7,2)=1, " week", " weeks")
When each row is a day and we need to convert 50 day into weeks in a large data set, how do we do it?
Can you please post some data examples here? That might help us to understand your question more clearer? Thank you!