CODEWITHSUNDEEP
excelexcel-formula
ssan
ssan

Reputation: 301

Finding Date difference based on the given value

I have a data which looks like below.

ID  Date    Currency
1   9/29/2015   INR
1   10/28/2015  INR
1   10/29/2015  INR
1   11/8/2015   EUR
1   11/11/2015  EUR
1   11/23/2015  EUR
1   11/24/2015  EUR
1   11/25/2015  INR
1   11/27/2015  EUR
1   12/1/2015   EUR
1   12/2/2015   CEZ
1   12/3/2015   CEZ
1   12/15/2015  CEZ
1   12/18/2015  INR
1   12/29/2015  INR

I want to calculate the difference between the date when the given the currency is same. For example, the first three are of same currency and therefor the difference will be calculated for the date 1 and date 3. Similarly, for rest of the rows I need to calculate. Precisely, the output looks like below.

ID  Date    Date2   Currency    Difference
1   9/29/2015   10/29/2015  INR 30
1   11/8/2015   11/24/2015  EUR 16
1   11/25/2015  11/25/2015  INR 0
1   11/27/2015  12/1/2015   EUR 4
1   12/2/2015   12/15/2015  CEZ 13
1   12/18/2015  12/29/2015  INR 11

I really appreciate if someone helps me to achieve this using excel

Upvotes: 0

Views: 50

Answers (1)

tyg
tyg

Reputation: 14892

Assuming the three columns are placed in column A, B and C and you start in row 1, then you can append two additional columns.

Cell D2, pull formula down:

=IF(C2=C1,D1,B2)

Cell E2, pull formula down:

=IF(C2=C3,"",B2-D2)

Column E now contains the desired result. For convenience, you can place a filter on the table to filter out the empty values in that column.

Upvotes: 1

Related Questions