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
***************************************************
Satya
Hi There,
Very good explanantion, simple, understandable, easily learnable.
Thank You so much:)
Regards,
Satya.