year by year headcount snapshot with only start and end date

[ulooz]

client would like "year by year snapshot" of headcount within company, assume that even if the start and end date was 1/2/2019 - 1/2/2020 that the person would count for 2019 and 2020. In the below example, i would need to summarize this contractor as counting for each 2017,2018,2019,2020,2021,2022 and 2023.

the data looks like this:

assignment # | name | start date | end date
1 | Darkwing Duck | 1/4/2017 | 3/4/2018
2 | Darkwing Duck | 4/20/2018 | 5/20/2019
3 | Darkwing Duck | 6/10/2019 | 7/8/2021
4 | Darkwing Duck | 8/4/20222 | 8/11/2023

 

[awoohaw]

what version of excel are you using?

Do you have a typo in row 4 column 3?

 

[awoohaw]

try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	assignment #	name	start date	end date		2017	2018	2019	2020	2021	2022	2023
2	1	Darkwing Duck	2017-01-04	2018-03-04		TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE
3	2	Darkwing Duck	2018-04-20	2019-05-20		FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
4	3	Darkwing Duck	2019-06-10	2021-07-08		FALSE	FALSE	TRUE	TRUE	TRUE	FALSE	FALSE
5	4	Darkwing Duck	2022-08-04	2023-08-11		FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE
ulooz

Cell Formulas
Range	Formula
F2:L5	F2	=AND(F$1>=YEAR($C2),F$1<=YEAR($D2))

 

[ulooz]

try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	assignment #	name	start date	end date		2017	2018	2019	2020	2021	2022	2023
2	1	Darkwing Duck	2017-01-04	2018-03-04		TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE
3	2	Darkwing Duck	2018-04-20	2019-05-20		FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
4	3	Darkwing Duck	2019-06-10	2021-07-08		FALSE	FALSE	TRUE	TRUE	TRUE	FALSE	FALSE
5	4	Darkwing Duck	2022-08-04	2023-08-11		FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE
ulooz

Cell Formulas
Range	Formula
F2:L5	F2	=AND(F$1>=YEAR($C2),F$1<=YEAR($D2))

Thanks! It worked, but, only for those assignments with a tenure of less than two years. If an assignment has a tenure of 2 years or more it does not work. Sorry I should have included that scenario in my examples.

 

[awoohaw]

Thanks! It worked, but, only for those assignments with a tenure of less than two years. If an assignment has a tenure of 2 years or more it does not work. Sorry I should have included that scenario in my examples.

Can you share some of those data examples?
And if at all possible try to post a mini workbook using the xl2bb add in (link below). This is what I used to post my suggestion above.

 

[ulooz]

Can you share some of those data examples?
And if at all possible try to post a mini workbook using the xl2bb add in (link below). This is what I used to post my suggestion above.

Sorry, you were correct... must have fat fingered the formula.

Next question - in order to roll this up in a pivot table or summary, (i.e. in 2018 there were 229 TRUE, in 2019 329 TRUE, etc...) I don't think a pivot can just do that, maybe a =countifs in a separate table?

 

[awoohaw]

please post a sample of your data. If I guess at what it looks like it may be wrong and it will be a waste of both of our time.
Use the xl2bb or just post a table. And please update your profile with what version of excel you are using. Some solutions will not work in earlier versions.

Thanks in advance.

 

[ulooz]

@awoohaw - So i'm trying to summarize that "AM", for example, will count for 2020 and 2021, hopefully the end result will be what I mentioned in my post above, summarizing # of people TRUE within each respective year. Hope it hasn't been a waste of your time and appreciate the help regardless!

example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1							2017	2018	2019	2020	2021	2022	2023
2	Assignment #	Name	Start_Date	End_Date	Min Start	Max End	2017	2018	2019	2020	2021	2022	2023
3	1	AM	1/1/2020	8/31/2020	1/1/2020	5/31/2021	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE
4	2	AM	1/1/2020	12/31/2020	1/1/2020	5/31/2021	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE
5	3	AM	9/1/2020	5/31/2021	1/1/2020	5/31/2021	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE
6	1	AS	6/1/2020	7/31/2020	3/6/2019	9/30/2023	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
7	2	AS	3/6/2019	11/30/2020	3/6/2019	9/30/2023	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
8	3	AS	3/16/2020	3/15/2022	3/6/2019	9/30/2023	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
9	1	AJ	12/3/2018	2/28/2019	12/3/2018	2/28/2019	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
10	1	AR	8/19/2019	2/16/2020	8/19/2019	12/1/2023	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
11	2	AR	2/17/2020	5/29/2020	8/19/2019	12/1/2023	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
12	3	AR	7/29/2021	12/1/2023	8/19/2019	12/1/2023	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
13	1	AS	10/1/2021	9/30/2023	3/6/2019	9/30/2023	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
14	1	AK	8/8/2017	12/16/2018	8/8/2017	3/17/2020	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
15	2	AK	12/17/2018	6/23/2019	8/8/2017	3/17/2020	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
16	3	AK	6/24/2019	2/23/2020	8/8/2017	3/17/2020	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
E3:E16	E3	=MINIFS(C:C,B:B,B3)
F3:F16	F3	=MAXIFS(D:D,B:B,B3)
G3:M16	G3	=AND(G$1>=YEAR($E3),G$1<=YEAR($F3))

 

[awoohaw]

It won't be a waste, thanks for the mini worksheet. hang tight. If I don't come up with a solution, I'm pretty sure someone else will.

 

[awoohaw]

Okay, I've looked trying to do a pivot chart. because your data is already essentially a cross tab, I can't figure it out. I'm sure there is a way to PIVOT it, but i'll try a different approach.