EXP: Differences Between 2 Dates In Excel

Excel function tutorials

Today I received an interesting question from a reader. He has a list of dates and needed to get the number of weeks and days between the two. Here is what he is looking at:

First off, I added a few more columns that you can see here:

The difference is obviously one minus the other. When you subtract dates in excel, it gives a date format. Ideally, I’d be looking at the number of days so I simply changed the format:

I get a number of days. I need to know how many weeks and obviously I do not want to have decimals and I need to round down in order to have the number of complete weeks. Here is how I’ll do it:

=ROUNDDOWN(D7/7,0)

This will round down to the number of weeks:

Then, the number of days will be:

Nb days = nb days – #weeks x 7

In this case:

=D7-E7*7

Then, I can simply add the difference as text by entering the following in G7:

=”The difference is ” & E7 & ” weeks and ” & F7 & ” days.”

You can see the result here:

Not that I want to be “fussy” but since this gives me a good opportunity, I’ll try to adjust text to account for the possibility that the number of days is 0 or 1.

If it is 0, I’d like to only write “X weeks”

If it is 1, I’d like to write “day” instead of “days”

So let’s start with the first condition. I’ll change:

=”The difference is ” & E7 & ” weeks and ” & F7 & ” days.”

To:

=”The difference is ” & E7 & ” weeks” & IF(F7=0,”.”,” and ” & F7 & ” days.”)

And the the final piece, by using a nested if condition:

=”The difference is “&E7&” weeks”&IF(F7=0,”.”,IF(F7=1,” and “&F7&” day.”,” and “&F7&” days.”))

You can see the end result here:

And download the spreadsheet here:)

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

One Feedback on "EXP: Differences Between 2 Dates In Excel"

Satya

Hi There,

Very good explanantion, simple, understandable, easily learnable.

Thank You so much:)

Regards,
Satya.