Automatically list years in between dates

[Limone]

Hi everyone.

I've got a table detailing employee information, including starting and expiring contract dates, with all the relative pivot tables and pie charts. I'm trying to find a way to dynamically get Excel to list every year in between the date the employee started working for us and the time their contract expired, so that the end user can eventually filter any pivot table they need by year and only get statistics that include people employed during a given time frame (e.g. "2017").

I'm open to suggestion if anyone can think of any other way to achieve the end goal (which would be filtering pivot tables by year based on starting and expiring contract dates only, no helper column).

 

[BrianGGG]

Can you post an example of the data and the pivot ?

 

[Limone]

http://imgur.com/a/i5KIC7A

Here you can see some examples. Columns D and E represent starting and expiring dates.

 

[Dave Patton]

Based on your initial post, I am not clear on what you require.
A few ideas are included for your consideration.

Excel 2010
	A	B	C	D	E	F	G
1	Commence date	5-May-10	2010		2010	2010	2010
2	Contract expired	10-Jan-18	2018		2011	2011	2011
3	Years		8		2012	2012	2012
4					2013	2013	2013
5					2014	2014	2014
6					2015	2015	2015
7					2016	2016	2016
8					2017	2017	2017
9					2018	2018	2018
10
11	T201812a	4b
4b

Cell Formulas
Range	Formula
E1	=YEAR(MIN($B$1:$B$2))
E2	=IF(E1MAX($B$1:$B$2)),E1+1,"")
E10	=IF(E9MAX($B$1:$B$2)),E9+1,"")
F1	=YEAR(B1)
F2	=IF(F1$B$2),F1+1,"")
F10	=IF(F9$B$2),F9+1,"")
G1	=C1
G2	=IF(G1<$C$2,G1+1,"")
G10	=IF(G9<$C$2,G9+1,"")
C1	=YEAR(B1)
C2	=YEAR(B2)
C3	=C2-C1