In Excel we tend to work in years, months or days. There may be times when you want to work in weeks.
Let’s say you want to find out how many weeks between two dates. You also want to calculate how many days for the part-week, if any.
In the image below we have start and end dates.
Let’s work our way through the calculations.
Finding the number of days between two dates is easy. Simply subtract the start date from the end date. The formula for cell C2 is
In Excel dates are held as numbers. So subtracting one date from another returns the number of days.
Weeks with a fraction is simple too. The formula for cell D2 is
This is the previous formula divided by 7. The decimal part is a fraction of a week.
Weeks and days
This one is a bit more complex as we want to include words in the result as well.
The formula for cell E2 is
=TRUNC((B2-A2)/7)&" weeks and "&MOD(B2-A2,7)&" days"
The TRUNC function truncates (cuts off) fractions from a number. It doesn’t round.
The & symbol joins text together.
The MOD function returns the remainder after dividing one number by another. In our case it returns the number of days left after dividing by 7.