Determining Years EE is Active Based on Hire/Term Dates

I feel this is something simple and I am just missing something here, but I wanted to figure out what years and Employee is Active based on Hire and Term Dates. My Spreadsheet is basically setup like below.

Please Login or Register  to view this content.

For this particular spreadsheet I want to figure out who was active in 2011, 2012 and 2013. I would like to return a value in the fourth column, that shows what years they are active. So my final product might look something like this.

Please Login or Register  to view this content.

I know i could write some sort of Macro for this, but i was hoping to see if i could do with just formulas if possible. Any help is greatly appreciated.

If you can live with a trailing , here and there, this not too horrible array formula can do it, in D2:

=IF(ISNUMBER(MATCH(2011,ROW(INDIRECT(YEAR(B2)&":"&IF(C2="Active", YEAR(TODAY()), YEAR(C2)))),0)),"2011,", "") &
IF(ISNUMBER(MATCH(2012,ROW(INDIRECT(YEAR(B2)&":"&IF(C2="Active", YEAR(TODAY()), YEAR(C2)))),0)),"2012,", "") &
IF(ISNUMBER(MATCH(2013,ROW(INDIRECT(YEAR(B2)&":"&IF(C2="Active", YEAR(TODAY()), YEAR(C2)))),0)),"2013", "")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

No, not without seeing it.

Attach a sample workbook. Make sure there is just enough data to demonstrate the problem.
Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Wow, I installed that formula in your workbook and it gets a #Value error right in the very first INDIRECT() evaluation of the YEAR in the E10. I've never seen that before. I guess we need to add additional plumbing to insure the years are treated as text initially.


=IF(ISNUMBER(MATCH(2011,ROW(INDIRECT(TEXT(YEAR(E10),"0000")&":"&IF(F10="Active", TEXT(YEAR(TODAY()),"000"), TEXT(YEAR(F10),"0000")))),0)),"2011,", "") &
IF(ISNUMBER(MATCH(2012,ROW(INDIRECT(TEXT(YEAR(E10),"0000")&":"&IF(F10="Active", TEXT(YEAR(TODAY()),"000"), TEXT(YEAR(F10),"0000")))),0)),"2012,", "") &
IF(ISNUMBER(MATCH(2013,ROW(INDIRECT(TEXT(YEAR(E10),"0000")&":"&IF(F10="Active", TEXT(YEAR(TODAY()),"000"), TEXT(YEAR(F10),"0000")))),0)),"2013,", "")

You can use the function Formulas > Evaluate Formula to watch that formula unfold, I use that tool daily to confirm my calculations are doing what they should.

Typically a formula runs through all the individual calculations one time. An array formula, properly constructed, can spit out numerous calculations in one formula where only one occurs normally.

To see this in its simplest form, put some random numbers in A1, A2, A3 and B1.
Then in an empty cell enter this regular formula, then use the Evaluate function to watch it calculate:
=SUM(A1*B1)

Now enter this array formula with CSE (Ctrl-Shift-Enter) in another cell, then Evaluate it:
=SUM(A1:A3*B1)

I always find it cool to see the {array} appear in the middle of the formula and start running multiple calculations.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.