Min Date

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
How can i find the minimum date? from the employee sheet?

column A in the employee sheet is the name that B2 has to match and column D in the employee sheet is the date that i want so how can i get this?

what i have but not working is :

=MIN('Employee sheet'!$A$2:$A$10000,B2,('Employee sheet'!$D$2:$D$10000))
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
How about

=MIN(IF('Employee sheet'!$A$2:$A$10000=B2,'Employee sheet'!$D$2:$D$10000))

IMPORTANT
  • This is an array formula
  • Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
  • If entered correctly, the formula will be enclosed in {brackets}
  • Do not enter the {brackets} manually
 

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
Not poss at mo but ive got this for max date but cant get it to work for min date a friend of mine did it for me but he's gone away for the hols

=LOOKUP(2,1/('Employee Sheet!$A$2:$A$10000=B2),'Employee Sheet'!$D$2:$D$10000)
 

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
ive got this for max date but cant get it to work for min date
F3 =MIN(IF($A$2:$A$15=B2,$D$2:$D$15))
G3 =MAX(IF($A$2:$A$15=B2,$D$2:$D$15))
H3 =LOOKUP(2,1/($A$2:$A$15=B2),$D$2:$D$15)

Out of this sample posted, what would you expect? I'm not sure why you think the formula you posted will find the last date, what it actually does is find the last occurrence in column A that matches B2 and then returns the relative answer from column D.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Jan</td><td style="text-align: center;;">Jan</td><td style="text-align: right;;"></td><td style="text-align: center;;">95</td><td style="text-align: right;;"></td><td style="text-align: center;;">Min</td><td style="text-align: center;;">Max</td><td style="text-align: center;;">Max</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Feb</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">44</td><td style="text-align: right;;"></td><td style="text-align: center;;">39</td><td style="text-align: center;;">95</td><td style="text-align: center;;">39</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Mar</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">63</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Apr</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">93</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">May</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Jun</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">51</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Jul</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Aug</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">62</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">Sep</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">Oct</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">96</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">Nov</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">90</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">Jan</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">39</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">Feb</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">73</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
 

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
cant paste image how do i do it?
 

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
This is the employee sheet

A E
1 Carol 10/11/15

2 James 11/11/15

3 Susan 12/11/15

4 Terry 13/11/15

5 Gary 14/11/15

6 Susan 15/11/15

7 Gary 16/11/15

8 Sara 17/11/15

9 Terry 18/11/15

10 Helen 19/11/15

What i want to be returned is Example

As Susan is in twice her minimum date is what i want returned which is number (3) 12/11/15 as it comes before number (8)

As Terry is in twice his minimum date is what i want returned which is number (4) 13/11/15 as it comes before number (9)

As Gary is in twice his minimum date is what i want returned which is number (5) 14/11/15 as it comes before number (7)

A & E Represent the Columns

hope this makes since
 

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
can edit previous post the names are in column A and the dates in column E
 

Watch MrExcel Video

Forum statistics

Threads
1,102,563
Messages
5,487,583
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top