# Number of days between entries

#### GoKats78

##### Board Regular
I have a data set in which I am looking to find the number of days between the two most recent entries based on the area in which the occurrence happened

Example ..the number of days between the last occurrence in Area A2 and the most recent previous one ..so in this case it would be 8/28/2014 minus 7/10/2014 = 49 days

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Marcelo Branco

##### MrExcel MVP
Maybe...

=SUMPRODUCT(LARGE((B2:B18="A2")*(A2:A18),{1;2})*{1;-1})

If you want you can substitute "A2" by a cell reference

M.

#### GoKats78

##### Board Regular
That did exactly what I needed it to do! the data set I provided wasn't exactly what I had ..but I was able to modify the provided formula to work!

That odd thing is if there is no match I was getting a number in the 40000's! with a little IF statement that went away.
Thanks so much for you help!

#### GoKats78

##### Board Regular
It works...but I really want to know exactly what each portion does...

#### Marcelo Branco

##### MrExcel MVP
It works...but I really want to know exactly what each portion does...

Select the cell with the formula and use the tool

Formulas > Evaluate formula

You can see, step by step, what the formula does

M.

#### GoKats78

##### Board Regular
I saw that...but I don't know what the {1;2})*{1;-1}) does..or why the formula needs them!
I need to figure that out so when I need to do one of these I can figure it out!

#### Marcelo Branco

##### MrExcel MVP
Take a look at Help file about the function LARGE
You see LARGE(array,k) where k is the position, i.e., 1 = Largest; 2 = 2nd_largest and so on

LARGE(.........,{1;2})
returns an array containing two values: the Largest value; and the 2nd_largest

*{1;-1) multiplies the Largest by 1 and the 2nd_largest by -1 resulting in
Largest - 2nd_largest

M.

Last edited:

#### GoKats78

##### Board Regular
I was just about to ask (I worked my way through the rest)..but was not sure what the 1,-1 did..I knew what it did...just not sure why!
One again..Thanks so much!

#### Marcelo Branco

##### MrExcel MVP
I was just about to ask (I worked my way through the rest)..but was not sure what the 1,-1 did..I knew what it did...just not sure why!
One again..Thanks so much!

You are welcome.

M.
ps: i edited my post above.

Replies
0
Views
777
Replies
1
Views
322
Replies
2
Views
352
Replies
3
Views
386
Replies
0
Views
473

1,191,095
Messages
5,984,637
Members
439,898
Latest member
Zack0611

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back