Monthly Headcount using Hiring & Termination Date

[Dees]

Hi Friends,

I need urgent help in creating a formula to get the Headcount information on a monthly basis. For example, I am looking for value 1 in Jan-08 column if the employee is current. Currently I am entering the data manually in each month. I need a formula where hiring date and termination date can be compared to each month and return a value 0 or 1. Please help urgently. Thanks in advance.

NAME Date Of Hire Date Of Termination Rehire Jan-08 Feb-08 Mar-08
Abby 08/31/09
Allen 05/29/07 10/15/08 1 1 1
Arwood 10/15/06 03/31/08 1 1 1
Ben 10/15/06 07/31/08 1 1 1
Beth 03/16/08 1


Regards,
Dees

 

[Sal Paradise]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">NAME</td><td style=";">Hidden Zero Date</td><td style=";">Hire</td><td style=";">Fire</td><td style=";">Rehire</td><td style="text-align: right;;">07-Jan</td><td style="text-align: right;;">07-Feb</td><td style="text-align: right;;">07-Mar</td><td style="text-align: right;;">07-Apr</td><td style="text-align: right;;">07-May</td><td style="text-align: right;;">07-Jun</td><td style="text-align: right;;">07-Jul</td><td style="text-align: right;;">07-Aug</td><td style="text-align: right;;">07-Sep</td><td style="text-align: right;;">07-Oct</td><td style="text-align: right;;">07-Nov</td><td style="text-align: right;;">07-Dec</td><td style="text-align: right;;">08-Jan</td><td style="text-align: right;;">08-Feb</td><td style="text-align: right;;">08-Mar</td><td style="text-align: right;;">08-Apr</td><td style="text-align: right;;">08-May</td><td style="text-align: right;;">08-Jun</td><td style="text-align: right;;">08-Jul</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">John</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2007/3/16</td><td style="text-align: right;;">2007/12/1</td><td style="text-align: right;;">2008/2/1</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">F1</font>),MONTH(<font color="Red">F1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">G1</font>),MONTH(<font color="Red">G1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">H1</font>),MONTH(<font color="Red">H1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">I1</font>),MONTH(<font color="Red">I1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">J1</font>),MONTH(<font color="Red">J1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">K1</font>),MONTH(<font color="Red">K1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">L1</font>),MONTH(<font color="Red">L1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">M1</font>),MONTH(<font color="Red">M1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">N1</font>),MONTH(<font color="Red">N1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">O1</font>),MONTH(<font color="Red">O1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Q1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">P1</font>),MONTH(<font color="Red">P1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">R1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">Q1</font>),MONTH(<font color="Red">Q1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">S1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">R1</font>),MONTH(<font color="Red">R1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">S1</font>),MONTH(<font color="Red">S1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">T1</font>),MONTH(<font color="Red">T1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">V1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">U1</font>),MONTH(<font color="Red">U1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">W1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">V1</font>),MONTH(<font color="Red">V1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">X1</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">W1</font>),MONTH(<font color="Red">W1</font>)+1,1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">F$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">G$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">H$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">I$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">J$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">K$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">L$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">M$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">N$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">O$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">P$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Q2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">Q$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">R2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">R$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">S2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">S$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">T$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">U$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">V2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">V$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">W2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">W$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">X2</th><td style="text-align:left">=IF(<font color="Blue">MOD(<font color="Red">MATCH(<font color="Green">X$1,$B$2:$E$2</font>),2</font>)=1,"",1</font>)</td></tr></tbody></table></td></tr></table><br />

The hidden column is necessary to prevent errors.

 

[Mel Smith]

Hi and welcome to the Board.

Perhaps you could use a formula to populate your Jan-08 column. Something along the lines of =IF(C1>1,1,0).

Substitute C1 for your termination date cell and copy down.

HTH

Mel

 

[Dees]

Thank you Sal & Mel! You guys are awesome. Can't thank you enough! I am so glad I joined this forum.

 

[maychui]

Hi All. I know this thread is quite old already but I am currently creating a monthly headcount report and i found the formulas Sal provided very useful.
However, in my company we count the headcount of a person even he/she joins in the middle of the month.
Using the excel example provided above, John's headcount will be counted in March already instead of starting in April. Please see below:-

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
NAME	Hidden Zero Date	Hire	Fire	Rehire
John			1

<thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]07-Jan[/TD]
[TD="align: right"]07-Feb[/TD]
[TD="align: right"]07-Mar[/TD]
[TD="align: right"]07-Apr[/TD]
[TD="align: right"]07-May[/TD]
[TD="align: right"]07-Jun[/TD]
[TD="align: right"]07-Jul[/TD]
[TD="align: right"]07-Aug[/TD]
[TD="align: right"]07-Sep[/TD]
[TD="align: right"]07-Oct[/TD]
[TD="align: right"]07-Nov[/TD]
[TD="align: right"]07-Dec[/TD]
[TD="align: right"]08-Jan[/TD]
[TD="align: right"]08-Feb[/TD]
[TD="align: right"]08-Mar[/TD]
[TD="align: right"]08-Apr[/TD]
[TD="align: right"]08-May[/TD]
[TD="align: right"]08-Jun[/TD]
[TD="align: right"]08-Jul[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]2007/3/16[/TD]
[TD="align: right"]2007/12/1[/TD]
[TD="align: right"]2008/2/1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</tbody>

May I know how i should adjust the formulas in order to reflect the headcount in March? Anyone can help out?
Thanks in advance.

Best,
May