Min Date
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 37

Thread: Min Date
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Sep 2015
    Location
    Ireland
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Min Date

    Row Column A Column D
    Employee Date
    1 Carol 10/11/2015
    2 James 11/11/2015
    3 Susan 12/11/2015
    4 Terry 13/11/2015
    5 Gary 14/11/2015
    6 Susan 15/11/2015
    7 Gary 16/11/2015
    8 Sara 17/11/2015
    9 Terry 18/11/2015
    10 Helen 19/11/2015


    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

  2. #12
    Board Regular
    Join Date
    Jan 2015
    Posts
    873
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Min Date

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

  3. #13
    Board Regular
    Join Date
    Sep 2015
    Location
    Ireland
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Min Date

    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)

  4. #14
    Board Regular
    Join Date
    Jan 2015
    Posts
    873
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Min Date

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

  5. #15
    Board Regular
    Join Date
    Aug 2015
    Posts
    766
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Min Date

    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

  6. #16
    Board Regular
    Join Date
    Sep 2015
    Location
    Ireland
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Min Date

    No joy im pasting the formula on a separate sheet and getting the answer based on my criteria from the employee sheet

  7. #17
    Board Regular
    Join Date
    Sep 2015
    Location
    Ireland
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Min Date

    Dont forget it has to get the info from employee sheet to be pasted in a separate sheet

  8. #18
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Posts
    5,143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Min Date

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

  9. #19
    Board Regular
    Join Date
    Jan 2015
    Posts
    873
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Min Date

    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 ?

  10. #20
    Board Regular
    Join Date
    Sep 2015
    Location
    Ireland
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Min Date

    Employee Sheet
    Row Column A Column D
    -- Employee Date
    1 Carol 10/11/2015
    2 James 11/11/2015
    3 Susan 12/11/2015
    4 Terry 13/11/2015
    5 Gary 14/11/2015
    6 Susan 15/11/2015
    7 Gary 16/11/2015
    8 Sara 17/11/2015
    9 Terry 18/11/2015
    10 Helen 19/11/2015
    Attendance Sheet

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

    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

Some videos you may like

User Tag List

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
  •