Min Date

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
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...
 

Some videos you may like

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).

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
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
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
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
Joined
Jul 28, 2004
Messages
5,145
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>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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></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: right;;"></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: right;;"></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: right;;"></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: right;;"></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: right;;"></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: right;;"></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;;">Sara</td><td style="text-align: right;;"></td><td style="text-align: center;;">17-Nov-15</td></tr></tbody></table><p style="width:9.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">Attendance Sheet</p><br /><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>Array Formulas</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">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">{=MIN(<font color="#0000FF">IF(<font color="#FF0000">'Employee Sheet'!$A$2:$A$11=B2,'Employee Sheet'!$D$2:$D$11</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">{=MIN(<font color="#0000FF">IF(<font color="#FF0000">'Employee Sheet'!$A$2:$A$11=B3,'Employee Sheet'!$D$2:$D$11</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">{=MIN(<font color="#0000FF">IF(<font color="#FF0000">'Employee Sheet'!$A$2:$A$11=B4,'Employee Sheet'!$D$2:$D$11</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">{=MIN(<font color="#0000FF">IF(<font color="#FF0000">'Employee Sheet'!$A$2:$A$11=B5,'Employee Sheet'!$D$2:$D$11</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">{=MIN(<font color="#0000FF">IF(<font color="#FF0000">'Employee Sheet'!$A$2:$A$11=B6,'Employee Sheet'!$D$2:$D$11</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">{=MIN(<font color="#0000FF">IF(<font color="#FF0000">'Employee Sheet'!$A$2:$A$11=B7,'Employee Sheet'!$D$2:$D$11</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,179
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))
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
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
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Attendance Sheet

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

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
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))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,179
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
Joined
Jul 28, 2004
Messages
5,145
See how this works, but you could also use the built in advanced filter feature to create a unique list of employee names.

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></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: right;;"></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: right;;"></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: right;;"></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: right;;"></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: right;;"></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: right;;"></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;;">Sara</td><td style="text-align: right;;"></td><td style="text-align: center;;">17-Nov-15</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Helen</td><td style="text-align: right;;"></td><td style="text-align: center;;">19-Nov-15</td></tr></tbody></table><p style="width:9.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">Attendance Sheet</p><br /><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>Array Formulas</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">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">{=IFERROR(<font color="#0000FF">INDEX(<font color="#FF0000">EmployeeList,SMALL(<font color="#00FF00">IF(<font color="#800080">FREQUENCY(<font color="#008080">IF(<font color="#FF00FF">EmployeeList<>"",MATCH(<font color="#000080">"~"&EmployeeList,EmployeeList&"",0</font>)</font>),ROW(<font color="#FF00FF">EmployeeList</font>)-ROW(<font color="#FF00FF">'Employee Sheet'!$A$2</font>)+1</font>)>0,ROW(<font color="#008080">EmployeeList</font>)-ROW(<font color="#008080">'Employee Sheet'!$A$2</font>)+1</font>),ROWS(<font color="#800080">B$2:B2</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<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
Joined
Sep 28, 2015
Messages
302
Office Version
365
Platform
Windows
That would be range date 1980 to 2015 - and range to 10000
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top