Min Date

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
388
Office Version
  1. 365
Platform
  1. 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))
 
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...
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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...

 
Upvote 0
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
 
Upvote 0
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
Cell Formulas
RangeFormula
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))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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?
 
Upvote 0
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
Cell Formulas
RangeFormula
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))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
EmployeeList='Employee Sheet'!$A$2:$A$11
 
Upvote 0
That would be range date 1980 to 2015 - and range to 10000
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top