Min Date
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 37

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

  1. #21
    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

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

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

    Default Re: Min Date

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


  3. #23
    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

    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

  4. #24
    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

    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



    Array Formulas
    CellFormula
    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))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Jeff

  5. #25
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Min Date

    Quote Originally Posted by Patcheen View Post
    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))
    Quote Originally Posted by jeffreybrown View Post
    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
    Quote Originally Posted by Patcheen View Post
    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...
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #26
    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

    This is what im looking for

    [QUOTE=jeffreybrown;4368560]I believe these are the exact results you are asking for.



    Excel 2007
    B C D
    1 Employee Date
    2 Carol 10-Nov-15
    3 James 11-Nov-15
    4 Susan 12-Nov-15
    5 Terry 13-Nov-15
    6 Gary 14-Nov-15
    7 Sara 17-Nov-15
    Attendance Sheet

    now to get the formulas to finish it..... i tried your formula but it returns 0

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

    Default Re: Min Date

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

  8. #28
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Min Date

    [QUOTE=Patcheen;4368571]This is what im looking for

    Quote Originally Posted by jeffreybrown View Post
    I believe these are the exact results you are asking for.



    Excel 2007
    B C D
    1 Employee Date
    2 Carol 10-Nov-15
    3 James 11-Nov-15
    4 Susan 12-Nov-15
    5 Terry 13-Nov-15
    6 Gary 14-Nov-15
    7 Sara 17-Nov-15
    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?
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #29
    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

    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



    Array Formulas
    CellFormula
    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))),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Workbook Defined Names
    NameRefers To
    EmployeeList='Employee Sheet'!$A$2:$A$11

    Jeff

  10. #30
    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

    That would be range date 1980 to 2015 - and range to 10000

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
  •