Results 1 to 9 of 9

Thread: Date Diff based on criteria

  1. #1
    Acolyte happy_smiler1's Avatar
    Join Date
    Aug 2012
    Posts
    55
    Articles
    0
    Excel Version
    office 365

    Date Diff based on criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I wanted to be able to find the difference between two dates based on whether one criteria is the same as the criteria above it if so calculate the date difference between, if it doesnt then leave it and go to the next. Please find an example of my data set below:

    e.g from my data set below - for customer id 1, row 4 would look up row 3 for customer id and because they match, it would calculate the date difference, same for row 3 to row 2, but row 2 doesnt have any data above it, it would automatically throw out a 0 against it.


    A B C

    Customer ID Date Amount
    1 01/01/2012 20
    1 02/01/2015 20
    1 03/01/2016 35
    3 12/12/2015 65
    3 13/12/2018 12
    5 23/01/2005 5
    5 15/05/2007 14
    12 14/02/2004 13
    12 15/02/2006 8
    12 16/02/2009 16
    12 17/02/2012 19
    13 16/05/2013 100
    13 17/05/2014 120
    13 18/05/2015 100


    Any help would be greatly appreciated.

    TIA

  2. #2
    Seeker Docharding's Avatar
    Join Date
    Dec 2018
    Posts
    6
    Articles
    0
    Excel Version
    2016
    I'm not sure I understand 100% but try the DATEDIF function for the days calculation

    Sent from my SM-G965F using Tapatalk

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,606
    Articles
    0
    Excel Version
    365
    in D2 copied down:
    =IF(A2=A1,DATEDIF(B1,B2,"d"),0)
    ?

  4. #4
    Acolyte happy_smiler1's Avatar
    Join Date
    Aug 2012
    Posts
    55
    Articles
    0
    Excel Version
    office 365
    Quote Originally Posted by p45cal View Post
    in D2 copied down:
    =IF(A2=A1,DATEDIF(B1,B2,"d"),0)
    ?
    Hi,

    I tried this, and it doesnt work any other ideas?

    Thanks

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,606
    Articles
    0
    Excel Version
    365
    'Doesn't work' isn't very informative.
    In what way is the following wrong?:
    Click image for larger version. 

Name:	2019-11-08_141641.jpg 
Views:	11 
Size:	30.8 KB 
ID:	9442

  6. #6
    Acolyte happy_smiler1's Avatar
    Join Date
    Aug 2012
    Posts
    55
    Articles
    0
    Excel Version
    office 365
    Quote Originally Posted by p45cal View Post
    'Doesn't work' isn't very informative.
    In what way is the following wrong?:
    Click image for larger version. 

Name:	2019-11-08_141641.jpg 
Views:	11 
Size:	30.8 KB 
ID:	9442
    It throws up #NUM! error when it gets to a row where the customer I'd matches but the dates are different.

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,606
    Articles
    0
    Excel Version
    365
    I only get #NUM! if the dates are not ascending, your sample data doesn't produce such an error.
    Sort the data primarily by ID, secondarily by date.

  8. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,670
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by p45cal View Post
    in D2 copied down:
    =IF(A2=A1,DATEDIF(B1,B2,"d"),0)
    ?
    Why would you use DATEDIF for days and not just subtract one from the other?

    =IF(A2=A1,B2-B1,0)

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,606
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Bob Phillips View Post
    Why would you use DATEDIF for days and not just subtract one from the other?

    =IF(A2=A1,B2-B1,0)
    True enough!

Posting Permissions

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