Difference between two dates in week and days

**newbiekinsey** · 06-05-2011, 03:49 PM

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

**TMS** · 06-05-2011, 06:48 PM

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

**newbiekinsey** · 06-05-2011, 07:09 PM

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

**TMS** · 06-05-2011, 08:16 PM

So, A1 = B1 and G5 = G4?

**newbiekinsey** · 06-06-2011, 05:18 AM

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

**Gary Brown** · 06-06-2011, 08:07 AM

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)

**TMS** · 06-06-2011, 03:44 PM

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