Page 1 of 4 123 ... LastLast
Results 1 to 10 of 37

Min Date

This is a discussion on Min Date within the Excel Questions forums, part of the Question Forums category; How can i find the minimum date? from the employee sheet? column A in the employee sheet is the name ...

  1. #1
    Board Regular
    Join Date
    Sep 2015
    Posts
    176

    Default Min Date

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

  2. #2
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,087

    Default Re: Min Date

    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
    Jeff

  3. #3
    Board Regular
    Join Date
    Sep 2015
    Posts
    176

    Default Re: Min Date

    Nope no joy but thank you

  4. #4
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,087

    Default Re: Min Date

    Can you post some sample data and the expected result?
    Jeff

  5. #5
    Board Regular
    Join Date
    Sep 2015
    Posts
    176

    Default Re: Min Date

    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)

  6. #6
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,087

    Default Re: Min Date

    Quote Originally Posted by Patcheen View Post
    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



    Jeff

  7. #7
    Board Regular
    Join Date
    Sep 2015
    Posts
    176

    Default Re: Min Date

    cant paste image how do i do it?

  8. #8
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,087
    Jeff

  9. #9
    Board Regular
    Join Date
    Sep 2015
    Posts
    176

    Default Re: Min Date

    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

  10. #10
    Board Regular
    Join Date
    Sep 2015
    Posts
    176

    Default Re: Min Date

    can edit previous post the names are in column A and the dates in column E

Page 1 of 4 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com