Calculate Days Rest

[JGriffo33]

I want to calculate days rest between two dates that are in the same column

A1 = Team
E1 = Date
K1 = Days Rest (formula needed here)

A2 = Team 1
A3 = Team 2
A4 = Team 3
A5 = Team 1
A6 = Team 3

E2 = 20/11/2016
E3 = 20/11/2016
E4 = 21/11/2016
E5 = 23/11/2016
E6 = 23/11/2016

K2 = 0
K3 = 0
K4 = 0
K5 = 2
K6 = 1

How do I get a formula that will recognise the team name in column A and see the dates in the range from column E and calculate the days rest, which is only between the dates not the days including?

 

[live_excel]

Hello JG,

please use this formula.
{=MAX(IF($A$2:$A$7=A2,$B$2:$B$7,""))-MIN(IF($A$2:$A$7=A2,$B$2:$B$7,""))}

It will work.

 

[JGriffo33]

Thanks mate, but the formula doesn't work on a bigger scale, I wrote my example as the first 5 entries, but I have data for at least 500 entries. Instead of $A$7 I have changed to $A$449 but the values come back as 335, 305, 274 etc. I'm not sure if it's because they are looking at dates and not calculating properly because I'm using Australian date format

 

[tygrrboi]

did you change both instances of A7 and both instances of B7 as well?

Since that is an array formula it needs to be input with CONTROL + SHIFT + ENTER instead of just enter. The {} curly braces do not get typed in manually but instead appear when you input an array formula properly. Have you done that?

 

[JGriffo33]

Yes I changed both A7 and B7, I also knew this was an array and used ctrl-shift-enter