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))
 
Row Column A Column D
EmployeeDate
1 Carol10/11/2015
2 James11/11/2015
3 Susan12/11/2015
4 Terry13/11/2015
5 Gary14/11/2015
6 Susan15/11/2015
7 Gary16/11/2015
8 Sara17/11/2015
9 Terry18/11/2015
10 Helen19/11/2015

<colgroup><col><col></colgroup><tbody>
</tbody>


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
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi,

Let say:

A1 is the first name (no headers)
E1 is the first date

Ctrl+Shift+Enter NOT just Enter

=MIN(IF(A1:A10=A1,E1:E10))
 
Upvote 0
But they are on a seperat e sheet called Employee sheet and the info is to me given in a different sheet

example =MIN(IF('Employee sheet'!$A$2:$A$10000=B2,'Employee sheet'!$D$2:$D$10000)) as shown in an earlier post (Jeffery Brown posted that formula)
 
Upvote 0
Sorry don't read the all story ..... Where is B2 ?
If let say B2 on Sheet1 , this formula should work .

=MIN(IF('Employee sheet'!A1:A10=Sheet1!B2,'Employee sheet'!E1:E10))
 
Upvote 0
Hi

Try

G2=INDEX(E2:E11,MATCH(1,IF(A2:A11=A5,1),0)) Control +shift+enter

Where E2:E11 range date
A2:A11 range name a5=Terry
 
Upvote 0
No joy im pasting the formula on a separate sheet and getting the answer based on my criteria from the employee sheet
 
Upvote 0
Dont forget it has to get the info from employee sheet to be pasted in a separate sheet
 
Upvote 0
The formula posted in post #2 does exactly what you ask.

With your sample data and ranges

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

Remember, this is a CSE formula.

You getting not joy doesn't help me much. What is happening? What is the formula returning? Are you sure B2 actually matches a value in the Employee sheet range A2:A11?

It may look like Gary in B2 matches Gary in the Employee sheet A6 or A8, but could there be spaces before on after the name?

Have you evaluated the formula to see what is happening?

If Gary is in B2 and the match for the Min value is in A6, in cell C2 (or any free cell) put =B2='Employee Sheet'!A6

What does it return? TRUE or FALSE

If TRUE then they match exactly, if FALSE then the name don't match and you now have to explore why. Maybe extra spaces...
 
Upvote 0
I still could not understand.


Ther is a sheet called "Employee sheet" where column A contains the employee names and column E contains the dates,
Is that correct?

Regarding B2 , What is the name of the sheet that B2 is there?

On which sheet formuls should be on ?

"... based on my criteria from the employee sheet" - which criteria ?
 
Upvote 0
Employee Sheet
Row Column A Column D
-- Employee Date
1 Carol10/11/2015
2 James11/11/2015
3 Susan12/11/2015
4 Terry13/11/2015
5 Gary14/11/2015
6 Susan15/11/2015
7 Gary16/11/2015
8 Sara17/11/2015
9 Terry18/11/2015
10 Helen19/11/2015


<tbody>
</tbody>
Attendance Sheet

Column B Column D (Example - carol is in B2, James B3) etc etc
EmployeeDate
Carol
James
Susan
Terry
Gary
Susan
Gary
Sara
Terry
Helen

<colgroup><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>

I want it to return the relevant date associated with the name i only want the date returned not the name


Carol would =10/11/2015
James would =11/11/2015
Susan would =12/11/2015
Terry would =13/11/2015
Gary would = 14/11/2015
Susan would =15/11/2015
Gary would =16/11/2015
Sara would =17/11/2015
Terry would =18/11/2015
Helen would =19/11/2015 The dates will show up in column D of the Attendance sheet
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
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