PDA

View Full Version : Solved: A function that pulls earliest date based on multiple criteria



Melinda
04-19-2007, 01:10 PM
I need to write a formula that pulls the earliest start date for each employee, an employee may have several lines, that has "n" as active and put this in a seperate table.For example, Christine Mills was paid $177.08 per pay period from 06/02/06 - 08/16/06. Christine Mills was paid $185.92
per pay period from 08/17/06 - 12/14/06. Christine Mills was paid $191.46 per pay period from 12/15/06 until the end of the fiscal year.(So that makes this record active(Y) and the first two records nonactive(N)).
I would like to be able to pull and list each, on seperate lines, of Christine's earliest nonactive(N) and active date(Y) start date.

View in a table:
N Christine Mills 06/02/06
Y Christine Mills 12/15/06

See attached excel file for example.

I have tried MATCH and LOOKUP but can not get it to work. Can anyone help me with a function that possible will work.

Melinda:help

mdmackillop
04-19-2007, 01:52 PM
Hi Melinda,
I've moved your post to the Excel forum.

Bob Phillips
04-19-2007, 01:58 PM
=MAX(IF(($D$2:$D$21=$D2)*($C$2:$C$21="N"),$N$2:$N$21))

and

=MAX(IF(($D$2:$D$21=$D2)*($C$2:$C$21="Y"),$N$2:$N$21))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

Melinda
04-20-2007, 05:42 AM
=MAX(IF(($D$2:$D$21=$D2)*($C$2:$C$21="N"),$N$2:$N$21))

and

=MAX(IF(($D$2:$D$21=$D2)*($C$2:$C$21="Y"),$N$2:$N$21))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.
Thanks xld. That did the trick. I have been working on this for several days and could not get it to work but you answer it in no time. You must know your stuff. I really your help and fast service.:thumb