+ Reply to Thread
Results 1 to 9 of 9

Difference between two dates in week and days

  1. #1
    Registered User
    Join Date
    06-05-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    4

    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


  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,206

    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
    Last edited by TMS; 06-05-2011 at 08:15 PM.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    06-05-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    4

    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

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,206

    Re: Difference between two dates in week and days

    So, A1 = B1 and G5 = G4?

  5. #5
    Registered User
    Join Date
    06-05-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    4

    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

  6. #6
    Forum Contributor
    Join Date
    12-02-2004
    Location
    London
    Posts
    255

    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)

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,206

    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

  8. #8
    Registered User
    Join Date
    05-11-2011
    Location
    College Station, Texas
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    42

    Re: Difference between two dates in week and days

    Hello,

    See if the file attached below, helps.

    Thanks and Regards,
    Abi
    Attached Files Attached Files

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,206

    Re: Difference between two dates in week and days

    See if the attachment helps


    Regards
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1