# Difference between two dates in week and days

1. ## Difference between two dates in week and days

I am trying to get an "exact" formula to work out the weeks and days between two date i.e.

Cell Ref A1 = 05/06/11 (today's date)
Cell Ref G5 = 30/06/11 (revised delivery date)

I have used formula below but it is not giving the correct information, it is making it 1 week extra

=INT((B1+1-G4)/7)&"week(s),"&MOD(B1+1-G4,7)&"day(s)"

i.e. 4 weeks 2 days when in fact it should be 3 weeks days

Any help with this would be gratefully received   Register To Reply

2. ## Re: Difference between two dates in week and days

Your formula does not seem to correspond to the cell references.

Please post a sample workbook so data and formula can be seen together in context.

Regards  Register To Reply

3. ## Re: Difference between two dates in week and days

It should ready G5 - sorry I typed it in incorrectly
Cell Ref A1 = 05/06/11 (today's date)
Cell Ref G5 = 30/06/11 (revised delivery date)

I have used formula below but it is not giving the correct information, it is making it 1 week extra

=INT((B1+1-G4)/7)&"week(s),"&MOD(B1+1-G4,7)&"day(s)"

i.e. 4 weeks 2 days when in fact it should be 3 weeks days

Hope this makes sense  Register To Reply

4. ## Re: Difference between two dates in week and days

So, A1 = B1 and G5 = G4?  Register To Reply

5. ## Re: Difference between two dates in week and days

I was obviously getting tired when I typed this out - correct message below, many apologies for the confusion

It should ready G5 - sorry I typed it in incorrectly
Cell Ref B1 = 05/06/11 (today's date)
Cell Ref G4 = 30/06/11 (revised delivery date)

I have used formula below but it is not giving the correct information, it is making it 1 week extra

=INT((B1+1-G4)/7)&"week(s),"&MOD(B1+1-G4,7)&"day(s)"

i.e. 4 weeks 2 days when in fact it should be 3 weeks days

I believe this is now correct and what I mean  Register To Reply

6. ## Re: Difference between two dates in week and days

Why not use the Datedif function as follows:-

=+INT(DATEDIF(A1,A2,"D")/7)&" week(s) "&MOD(DATEDIF(A1,A2,"D"),7)&" day(s)"

Where A1 = 5th June 2011 and A2 = 30th June 2011

This will give the answer 3 weeks and 4 days (i.e. 25 days between 5th June and 30th June)  Register To Reply

7. ## Re: Difference between two dates in week and days

As G4 is greater than B1, that is, in the future, I would have thought the formula would be more like:

=INT((G4-B1+1)/7)&"week(s),"&MOD(G4-B1+1,7)&"day(s)"

which gives: 3week(s),5day(s)

or, using Gary's formula:

=INT(DATEDIF(B1,G4,"D")/7) & " week(s) " & MOD(DATEDIF(B1,G4,"D"),7) & " day(s)"

giving: 3 week(s) 4 day(s)

Note that your formula actually showed as: -4week(s),4day(s) ... that is, a negative number of weeks/days.

Regards  Register To Reply

8. ## Re: Difference between two dates in week and days

Hello,

See if the file attached below, helps.

Thanks and Regards,
Abi  Register To Reply

9. ## Re: Difference between two dates in week and days

See if the attachment helps

Regards  Register To Reply