HR data: Employee count using start date and leaving date

[chloe123]

Hi,

I'm very new to excel and would appreciate help .

I have a list of 48 employees. some current, some left.

The info i have on each is start date and leaving date. (screenshot attached).

i would like a chart that shows how many total active employees there are and how many new starters there are in each quarter since 2017.

I am COMPLETELY lost and have been trying all day with date tables, measures... I just cant get anywhere close.

I would be so so grateful for any help.

 

[Toadstool]

Hi Chloe123,

Some have no Leaving Date which suggests they're still employed. Please confirm.
Those with a Leaving Date have no Start Date so how can we tell if they started the month before or in 2016?

 

[chloe123]

Hi Chloe123,

Some have no Leaving Date which suggests they're still employed. Please confirm.
Those with a Leaving Date have no Start Date so how can we tell if they started the month before or in 2016?

Hi Toadstool,

Thanks so much for your reply!

Sorry I added this late last night just as an example - there should be start dates in all columns . for an example, lets just say the ones where there are gaps all started on 01/01/2017?

Hope this helps!

 

[chloe123]

Hi Toadstool,

Thanks so much for your reply!

Sorry I added this late last night just as an example - there should be start dates in all columns . for an example, lets just say the ones where there are gaps all started on 01/01/2017?

Hope this helps!

I've attached a new screenshot. Dates in all join dates. 4 employees have no leave date so are still employed. Thanks

 

[chloe123]

I've attached a new screenshot. Dates in all join dates. 4 employees have no leave date so are still employed. Thanks

I've got as far as making a pivot table with a count of join dates (new hires) and count of leave dates (leavers).

It would be great to have a cumulative running total and/or a way to turn it into turnover.

Again, thank you so much!

 

[JGordon11]

Recommend using Tables so your chart will automatically update when you add data. Ctrl-T with a table cell selected will create a table out of your data. I called the first table EmployeeData and the second ChartData (with a table cell selected click design under table and rename the tables). The dates in ChartData can be automatically added by first manually entering the first two qtrs start and end dates, selecting those four cells and dragging down (Excel recognizes the pattern). Once ChartData table is working create the chart by selecting Currently Employed hold the ctrl key down, select Q1-2016 to Q1-2021, keep holding the ctrl down and click on the count of current employees and the hire counts. That should have selected all the chart data. Then Insert bar chart should create a bar chart similar to the one below. When a new quarter starts just select the last two quarter start and end dates and drag the fill handle down a row.

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		Name	Start	End		Currently Employed	11
3		A	1/27/2017	1/22/2020
4		B	5/10/2020	3/15/2021		Quarter	Hires	Qtr Start	Qtr End
5		C	7/3/2017	11/16/2017		Q1-2017	3	1/1/2017	3/31/2017
6		D	2/19/2017			Q2-2017	1	4/1/2017	6/30/2017
7		E	6/14/2017			Q3-2017	2	7/1/2017	9/30/2017
8		F	4/12/2019			Q4-2017	0	10/1/2017	12/31/2017
9		G	10/13/2019	2/9/2020		Q1-2018	1	1/1/2018	3/31/2018
10		H	7/17/2019	8/12/2020		Q2-2018	1	4/1/2018	6/30/2018
11		I	2/19/2020	9/26/2020		Q3-2018	0	7/1/2018	9/30/2018
12		J	1/7/2021			Q4-2018	0	10/1/2018	12/31/2018
13		K	8/29/2019	11/29/2020		Q1-2019	1	1/1/2019	3/31/2019
14		L	8/14/2016	1/30/2018		Q2-2019	1	4/1/2019	6/30/2019
15		M	2/4/2017	7/6/2018		Q3-2019	3	7/1/2019	9/30/2019
16		N	12/26/2019			Q4-2019	4	10/1/2019	12/31/2019
17		O	1/17/2019			Q1-2020	1	1/1/2020	3/31/2020
18		P	8/15/2017			Q2-2020	1	4/1/2020	6/30/2020
19		Q	6/18/2018			Q3-2020	0	7/1/2020	9/30/2020
20		R	3/6/2018	10/1/2019		Q4-2020	0	10/1/2020	12/31/2020
21		S	12/10/2019	3/22/2021		Q1-2021	2	1/1/2021	3/31/2021
22		T	3/30/2021
23		U	8/1/2019	4/6/2020
24		V	12/20/2016	4/13/2018
25		W	12/16/2019	2/15/2021
26		X	1/2/2022
27		Y	11/11/2021
28
Sheet4

Cell Formulas
Range	Formula
H2	H2	=COUNTIF(EmployeeData[End],"")
G5:G21	G5	="Q" & MONTH(J5)/3 & "-" & YEAR(J5)
H5:H21	H5	=COUNTIFS(EmployeeData[Start],">=" & I5,EmployeeData[Start],"<=" & J5)

 

[Toadstool]

Hi Chloe123,

Here's my method

Chloe123.xlsx
	A	B	C	D	E	F	G	H	I
1	First Name	Joining Date	Leave Date		Qtr Dates		Quarter	Staff	Starters
2	Elisa	05-Jun-17	22-Mar-19		01-Jan-17		2017-Q1	8	3
3	Daniele	18-Jan-17	24-Jan-20		01-Apr-17		2017-Q2	9	1
4	Tony	24-Apr-13			01-Jul-17		2017-Q3	9	0
5	Nicholas	07-Mar-16			01-Oct-17		2017-Q4	9	0
6	Filippo	22-Mar-17			01-Jan-18		2018-Q1	8	0
7	Benjamin	12-Jul-16			01-Apr-18		2018-Q2	8	0
8	Maciej	21-Mar-17	15-May-20		01-Jul-18		2018-Q3	8	0
9	Jan	03-May-16	09-Feb-18		01-Oct-18		2018-Q4	8	0
10	Enrico	12-Feb-16	09-May-19		01-Jan-19		2019-Q1	7	0
11					01-Apr-19		2019-Q2	6	0
12					01-Jul-19		2019-Q3	6	0
13					01-Oct-19		2019-Q4	6	0
14					01-Jan-20		2020-Q1	5	0
15					01-Apr-20		2020-Q2	4	0
16					01-Jul-20		2020-Q3	4	0
17					01-Oct-20		2020-Q4	4	0
18					01-Jan-21		2021-Q1	4	0
19					01-Apr-21		2021-Q2	4	0
Sheet1

Cell Formulas
Range	Formula
G2:G19	G2	=YEAR(E2)&"-Q"&ROUND(MONTH(E2)/4,0)+1
H2:H19	H2	=COUNTIFS($B$2:$B$99,"<="&E3,$C$2:$C$99,">="&E3)+COUNTIFS($B$2:$B$99,"<="&E3,$C$2:$C$99,"")
I2:I19	I2	=COUNTIFS($B$2:$B$99,">="&E2,$B$2:$B$99,"<"&E3)
E3:E19	E3	=EDATE(E2,3)

...and an Insert, Recommednded Chart gives: