# Min Date

#### jeffreybrown

##### Well-known Member
Nope no joy but thank you
Suddenly this has turned into me, no joy.

I'll try one more time to understand.

How can i find the minimum date? from the employee sheet?
You started the thread with the above, but now in your last post, you are showing a date for every employee, not just the Min.

That is,

Susan would =12/11/2015
Susan would =15/11/2015

Terry would =13/11/2015
Terry would =18/11/2015

Gary would = 14/11/2015
Gary would =16/11/2015

Since the names above are listed twice, why are your expecting two dates for the duplicate name?

You threw Max into the thread at one point, but I'm really befuddled as too what we are trying to accomplish here outside of the MIN(IF construct you have been given.

I'm losing energy on continuing in this thread as you did not even answer any of my last questions.

How is what you last posted for the Employee sheet any different from the results you cite for the Attendance sheet?

All names and dates are listed in both places...

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

##### Well-known Member
A different story in D2 Attendance Sheet Enter (and drag down)

=INDEX('Employee Sheet'!D2:\$D\$11,MATCH('Employee Sheet'!A2:\$A\$11,'Attendance Sheet'!A2,0))

BTW

"1 Carol" should be "Carol" in the both sheets and so on...

#### Patcheen

##### Active Member
i only want the minimum date if ive made errors my apologies.......its wrecking my head and all of your now.... im trying to explain and answer questions my apologies again if ive forgotten.

no ive no spaces and the date returned should be the minimum not the max as well as i stated in my previous that was an error on my part....

susan should be the 12/11/1015 - Terry should be 13/11/15 and gary 14/11/15 and not the other dates.

ive tried this it returns d name it dont return true or false =MIN(IF(A1:A10=A1,E1:E10)) but i have to do this in the employee sheet i want it from the employee sheet to the attendance sheet

#### jeffreybrown

##### Well-known Member
I believe these are the exact results you are asking for.

Is something wrong with these results with only collecting the Min?

<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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Employee</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Date</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Carol</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">10-Nov-15</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">James</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">11-Nov-15</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Susan</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">12-Nov-15</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Terry</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">13-Nov-15</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Gary</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">14-Nov-15</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Susan</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">15-Nov-15</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Gary</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">16-Nov-15</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Sara</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">17-Nov-15</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">Terry</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">18-Nov-15</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">Helen</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">19-Nov-15</td></tr></tbody></table><p style="width:8.4em;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">Employee Sheet</p><br /><br />

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

##### MrExcel MVP
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))

=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
i only want the minimum date if ive made errors my apologies.......its wrecking my head and all of your now.... im trying to explain and answer questions my apologies again if ive forgotten.

no ive no spaces and the date returned should be the minimum not the max as well as i stated in my previous that was an error on my part....

susan should be the 12/11/1015 - Terry should be 13/11/15 and gary 14/11/15 and not the other dates.

ive tried this it returns d name it dont return true or false =MIN(IF(A1:A10=A1,E1:E10)) but i have to do this in the employee sheet i want it from the employee sheet to the attendance sheet
Create a small sample and post it here along with the desired results...

#### Patcheen

##### Active Member
This is what im looking for

I believe these are the exact results you are asking for.

Excel 2007
BCD
1EmployeeDate
2Carol10-Nov-15
3James11-Nov-15
4Susan12-Nov-15
5Terry13-Nov-15
6Gary14-Nov-15
7Sara17-Nov-15

</tbody>
Attendance Sheet

now to get the formulas to finish it..... i tried your formula but it returns 0

##### Well-known Member
Attendance Sheet B2 = (that is for the uniqe names)
Ctrl+Shift+Enter NOT just Enter

=IFERROR(IFERROR(INDEX('Employee Sheet'!A2:A11, MATCH(0, COUNTIF(\$B\$1:B1, 'Employee Sheet'!A2:A11), 0)), INDEX('Employee Sheet'!A2:A11, MATCH(0, COUNTIF(\$B\$1:B1,'Employee Sheet'!A2:A11), 0))), "")

Attendance Sheet D2 = (that is for the min dates)
Ctrl+Shift+Enter NOT just Enter

=MIN(IF('Employee Sheet'!\$A\$2:\$A\$11=B2,'Employee Sheet'!\$D\$2:\$D\$11))

##### MrExcel MVP
This is what im looking for

I believe these are the exact results you are asking for.

Excel 2007
BCD
1EmployeeDate
2Carol10-Nov-15
3James11-Nov-15
4Susan12-Nov-15
5Terry13-Nov-15
6Gary14-Nov-15
7Sara17-Nov-15

<tbody>
</tbody>
Attendance Sheet

now to get the formulas to finish it..... i tried your formula but it returns 0
What is your real range of dates in your attendance sheet? And what is your real range of employees in your attendance sheet?

#### jeffreybrown

##### Well-known Member
See how this works, but you could also use the built in advanced filter feature to create a unique list of employee names.

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">EmployeeList</th><td style="text-align:left">='Employee Sheet'!\$A\$2:\$A\$11</td></tr></tbody></table></td></tr></table><br />

#### Patcheen

##### Active Member
That would be range date 1980 to 2015 - and range to 10000

1,102,095
Messages
5,484,646
Members
407,459
Latest member
DICKSON KIMEMIA

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...