# Thread: Min Date Thanks: 0 Likes: 0

1. ## Re: Min Date

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

2. ## Re: Min Date

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

3. ## Re: Min Date

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

4. ## Re: Min Date

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

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

Excel 2007
ABCD
1EmployeeDate
2Carol10-Nov-15
3James11-Nov-15
4Susan12-Nov-15
5Terry13-Nov-15
6Gary14-Nov-15
7Susan15-Nov-15
8Gary16-Nov-15
9Sara17-Nov-15
10Terry18-Nov-15
11Helen19-Nov-15

Employee Sheet

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

Attendance Sheet

Array Formulas
CellFormula
D2{=MIN(IF('Employee Sheet'!\$A\$2:\$A\$11=B2,'Employee Sheet'!\$D\$2:\$D\$11))}
D3{=MIN(IF('Employee Sheet'!\$A\$2:\$A\$11=B3,'Employee Sheet'!\$D\$2:\$D\$11))}
D4{=MIN(IF('Employee Sheet'!\$A\$2:\$A\$11=B4,'Employee Sheet'!\$D\$2:\$D\$11))}
D5{=MIN(IF('Employee Sheet'!\$A\$2:\$A\$11=B5,'Employee Sheet'!\$D\$2:\$D\$11))}
D6{=MIN(IF('Employee Sheet'!\$A\$2:\$A\$11=B6,'Employee Sheet'!\$D\$2:\$D\$11))}
D7{=MIN(IF('Employee Sheet'!\$A\$2:\$A\$11=B7,'Employee Sheet'!\$D\$2:\$D\$11))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

5. ## Re: Min Date

Originally Posted by Patcheen
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))
Originally Posted by jeffreybrown

=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
Originally Posted by Patcheen
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...

6. ## Re: Min Date

This is what im looking for

[QUOTE=jeffreybrown;4368560]I believe these are the exact results you are asking for.

Excel 2007
B C D
1 Employee Date
2 Carol 10-Nov-15
3 James 11-Nov-15
4 Susan 12-Nov-15
5 Terry 13-Nov-15
6 Gary 14-Nov-15
7 Sara 17-Nov-15
Attendance Sheet

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

7. ## Re: Min Date

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

8. ## Re: Min Date

[QUOTE=Patcheen;4368571]This is what im looking for

Originally Posted by jeffreybrown
I believe these are the exact results you are asking for.

Excel 2007
B C D
1 Employee Date
2 Carol 10-Nov-15
3 James 11-Nov-15
4 Susan 12-Nov-15
5 Terry 13-Nov-15
6 Gary 14-Nov-15
7 Sara 17-Nov-15
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?

9. ## Re: Min Date

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

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

Attendance Sheet

Array Formulas
CellFormula
B2{=IFERROR(INDEX(EmployeeList,SMALL(IF(FREQUENCY(IF(EmployeeList<>"",MATCH("~"&EmployeeList,EmployeeList&"",0)),ROW(EmployeeList)-ROW('Employee Sheet'!\$A\$2)+1)>0,ROW(EmployeeList)-ROW('Employee Sheet'!\$A\$2)+1),ROWS(B\$2:B2))),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Workbook Defined Names
NameRefers To
EmployeeList='Employee Sheet'!\$A\$2:\$A\$11

10. ## Re: Min Date

That would be range date 1980 to 2015 - and range to 10000

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•