Need a formula for returning row if employee worked in a certain year

[Particle Noun]

I've been knocking this about all day, and am coming up short.

In brief, lets say I have the following:

Column A has an employee name.
Column B has their original hire date. (i.e. 11/12/2007)
Column C has their termination date. (i.e. 09/14/2014)

I am trying to separate an enormous spreadsheet into one which shows all employees who were employed during 2011, 2012, 2013 and 2014. In the example above (11/12/2007 through 9/14/14) that person should show up on queries for 2011, 2012, 2013, and 2014.

I am looking to find a way to create a formula which would look at the range of dates between Column B and C, and set a variable that says only return row if 2011 is included in that range. There are all sorts of reasons my various attempts haven't worked, but I'm nagged with the feeling that this should be easier than I'm making it.

Any assistance would be invaluable!

[FDibbins]

Hi, welcome to the forum

A formula for this would be fairly simple, but have you considered using filters (very basic) or a Pivot Table (basic/intermediate)?

If this is something you thing would help, let me know and I can walk you through the process - if you dont already know how

[JeteMc]

Here is an example of a formula that you could use:

Please Login or Register  to view this content.

Having very little idea of how your query would be arranged I took the liberty of attaching a small worksheet to show how the formula might be applied:
Return row if employed.xlsx
Let me know if you have any questions.

[MarvinP]

Hi Particle Noun and welcome to the forum,

I envision you problem like the attached. I've created some random hire and end dates and then build a table to the left to show if they were working on the first of that year. Then down at the bottom is the count of employees working on those dates. See if this helps.

[MarvinP]

JeteMc has a much better answer that uses the end of year date.

See if his answer works better than mine. Try

Please Login or Register  to view this content.

Also, I have a small problem with this problem and my answer. If an employee start date was 12/30/2015, should they be counted as having worked in 2015?

[Particle Noun]

Thanks everyone for the responses, it has been so helpful! In the end, I used a combination of Marvin's and JeteMc's formula, with a few extra tweaks:

=IF(AND($G2<=DATE(X$1,12,31),(IF(ISBLANK($I2), "12/7/2015", $I2))>=DATE(X$1,1,1)),X$1,""). I found that a number of employee's didn't have a termination date, so added in the bit to change any blank determination dates to today's date (which doesn't have to update each time I open it, so I just hardcoded that date).

I can't thank you all enough, what a great community!