Weeks and days calculation in Excel

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.

Days

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

=B2-A2

In Excel dates are held as numbers. So subtracting one date from another returns the number of days.

 

Weeks

Weeks with a fraction is simple too. The formula for cell D2 is

=(B2-A2)/7

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.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.