# Thread: Date Diff based on criteria

1. ## Date Diff based on criteria

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. I'm not sure I understand 100% but try the DATEDIF function for the days calculation

Sent from my SM-G965F using Tapatalk

3. in D2 copied down:
=IF(A2=A1,DATEDIF(B1,B2,"d"),0)
?

4. Originally Posted by p45cal
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. 'Doesn't work' isn't very informative.
In what way is the following wrong?:

6. Originally Posted by p45cal
'Doesn't work' isn't very informative.
In what way is the following wrong?:
It throws up #NUM! error when it gets to a row where the customer I'd matches but the dates are different.

7. 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. Originally Posted by p45cal
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. Originally Posted by Bob Phillips
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
•