# Calculate Days Rest

#### JGriffo33

##### New Member
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?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### live_excel

##### New Member
Hello JG,

{=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

##### New Member
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

##### Well-known Member
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?

Last edited:

#### JGriffo33

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

Replies
1
Views
644
Replies
4
Views
185
Replies
1
Views
385
Replies
0
Views
192
Replies
5
Views
394

1,195,671
Messages
6,011,075
Members
441,581
Latest member
rp4717

### 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