Way to retrieve employees working during a given year, pivot table filtering

[Limone]

Hi everyone.

I've got a table detailing all employee contract information, including hiring and quitting dates, with all the relative pie charts and pivot tables. I'm trying to find a way to filter the pivot tables so that when someone selects a fiscal year (e.g. "2018"), either in the pivot table or outside of it (but not in the employee table), the only information I get are about employees working in that time frame (including those that may have stopped working for us at some point during that same year or that have been hired right at the end of it).

I'd really want not to have to resort to ways that include lots of helper column in the employee table.

Thanks everyone in advance. I might not be able to report back until Dec 27th.

 

[Limone]

I've figured out a formula to retrieve the right employees based on a given year but I'm not sure how to incorporate it into the pivot table.

Since the referenced table is about employees and not the company, the cell containing the year would need to be outside of the pivot table (I'm working on Excel 2007): I have a site detailing a VBA code that lets you insert a report filter based on an external cell (https://www.extendoffice.com/documents/excel/5325-excel-pivot-table-filter-based-on-cell-value.html), but I'm not sure I'd be able to base a formula to it to make everything works.