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

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
Not poss at mo but ive got this for max date but cant get it to work for min date a friend of mine did it for me but he's gone away for the hols

=LOOKUP(2,1/('Employee Sheet!$A$2:$A$10000=B2),'Employee Sheet'!$D$2:$D$10000)
 
Upvote 0
ive got this for max date but cant get it to work for min date

F3 =MIN(IF($A$2:$A$15=B2,$D$2:$D$15))
G3 =MAX(IF($A$2:$A$15=B2,$D$2:$D$15))
H3 =LOOKUP(2,1/($A$2:$A$15=B2),$D$2:$D$15)

Out of this sample posted, what would you expect? I'm not sure why you think the formula you posted will find the last date, what it actually does is find the last occurrence in column A that matches B2 and then returns the relative answer from column D.


Excel 2007
ABCDEFGH
2JanJan95MinMaxMax
3Feb44399539
4Mar63
5Apr93
6May1
7Jun51
8Jul31
9Aug62
10Sep33
11Oct96
12Nov70
13Dec90
14Jan39
15Feb73
Sheet1
 
Upvote 0
This is the employee sheet

A E
1 Carol 10/11/15

2 James 11/11/15

3 Susan 12/11/15

4 Terry 13/11/15

5 Gary 14/11/15

6 Susan 15/11/15

7 Gary 16/11/15

8 Sara 17/11/15

9 Terry 18/11/15

10 Helen 19/11/15

What i want to be returned is Example

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
can edit previous post the names are in column A and the dates in column E
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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