IF Formula

[Toastis]

I have a Hire Date and a Termination Date Column. I need to determine the number of employees that were employed (according to these dates) in January 2011. I will repeat the calculation for Feb, Mar, etc.

The formula would need to include those who may have worked in the month and also been terminated in the same month hired.

Ultimately I need a Starting Headcount for the month and an Ending Headcount for the month.

Those still employed here have a term date of 12/31/2049.

Can anyone help?
<b>Excel 2007</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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Driver ID</td><td style=";">Hire Date Only</td><td style=";">Termination Date</td><td style=";">Jan Start</td><td style=";">Jan End </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">CAUMO01</td><td style="text-align: right;;">01/24/2011</td><td style="text-align: right;;">01/26/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">ADAJU</td><td style="text-align: right;;">09/16/1996</td><td style="text-align: right;;">12/31/2049</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">ADABR</td><td style="text-align: right;;">11/07/2005</td><td style="text-align: right;;">12/31/2049</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">ADAAA</td><td style="text-align: right;;">06/15/2007</td><td style="text-align: right;;">12/31/2049</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">AGUJO</td><td style="text-align: right;;">08/05/2010</td><td style="text-align: right;;">07/19/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">AGUMA</td><td style="text-align: right;;">09/20/1999</td><td style="text-align: right;;">03/18/2010</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">AGUMI</td><td style="text-align: right;;">07/21/2011</td><td style="text-align: right;;">12/31/2049</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">AGURO</td><td style="text-align: right;;">08/15/2007</td><td style="text-align: right;;">12/31/2049</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">AKEHA</td><td style="text-align: right;;">10/11/2002</td><td style="text-align: right;;">12/31/2049</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">ALLRO</td><td style="text-align: right;;">11/18/1998</td><td style="text-align: right;;">12/31/2049</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">ALVJO</td><td style="text-align: right;;">04/04/2008</td><td style="text-align: right;;">12/31/2049</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">ANDGO</td><td style="text-align: right;;">03/09/2007</td><td style="text-align: right;;">05/14/2010</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">ANDJO</td><td style="text-align: right;;">05/22/2006</td><td style="text-align: right;;">04/01/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">ANDMI</td><td style="text-align: right;;">04/03/2000</td><td style="text-align: right;;">02/07/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">ANDRO</td><td style="text-align: right;;">09/15/2007</td><td style="text-align: right;;">12/31/2049</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">ANTJI</td><td style="text-align: right;;">10/20/2010</td><td style="text-align: right;;">12/31/2049</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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 />

 

[taigovinda]

One way, try these and copy down:

Q2: =--AND(O2<"1/1/2011"+0,P2>"1/1/2011"+0)
R2: =--AND(O2<"1/31/2011"+0,P2>"1/31/2011"+0)

...and add up the 1s.

Alternately, in any cell:

Jan Start total: =SUMPRODUCT(--(O2:O7<"1/1/2011"+0),--(P2:P7>"1/1/2011"+0))
Jan End total: =SUMPRODUCT(--(O2:O7<"1/31/2011"+0),--(P2:P7>"1/31/2011"+0))

...this way you wouldn't need the helper column, but you only get the totals.

 

[njimack]

Is this what you're after?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Driver ID	Hire Date Only	Termination Date			01/01/2011	01/02/2011	01/03/2011	01/04/2011	01/05/2011	01/06/2011	01/07/2011	01/08/2011	01/09/2011	01/10/2011	01/11/2011	01/12/2011
2	CAUMO01	24/01/2011	26/01/2011		No employed:	11	10	10	10	10	10	11	10	10	10	10	10
3	ADAJU	16/09/1996	31/12/2049
4	ADABR	07/11/2005	31/12/2049
5	ADAAA	15/06/2007	31/12/2049
6	AGUJO	05/08/2010	19/07/2011
7	AGUMA	20/09/1999	18/03/2010
8	AGUMI	21/07/2011	31/12/2049
9	AGURO	15/08/2007	31/12/2049
10	AKEHA	11/10/2002	31/12/2049
11	ALLRO	18/11/1998	31/12/2049
12	ALVJO	04/04/2008	31/12/2049
13	ANDGO	09/03/2007	14/05/2010
14	ANDJO	22/05/2006	01/04/2009
15	ANDMI	03/04/2000	07/02/2009
16	ANDRO	15/09/2007	31/12/2049
17	ANTJI	20/10/2010	31/12/2049
Sheet1

 

[Toastis]

One way, try these and copy down:

Q2: =--AND(O2<"1/1/2011"+0,P2>"1/1/2011"+0)
R2: =--AND(O2<"1/31/2011"+0,P2>"1/31/2011"+0)

...and add up the 1s.

Alternately, in any cell:

Jan Start total: =SUMPRODUCT(--(O2:O7<"1/1/2011"+0),--(P2:P7>"1/1/2011"+0))
Jan End total: =SUMPRODUCT(--(O2:O7<"1/31/2011"+0),--(P2:P7>"1/31/2011"+0))

...this way you wouldn't need the helper column, but you only get the totals.

Thanks - This worked as far as I can tell Thank you again.

 

[MrVillareal]

Hi friend can you try this one.

I convert your column heading jan start into date such as 1/1/2011 and january end to 31/1/2011 and for february end to 28/2/2011 and so on up to 31/12/2011.

This is the formula:

For Q2= IF(AND($O2<=Q$1,$P2>=Q$1),1,0) copy down.

For

R2=IF(AND($O2<=R$1,$P2>=R$1),1,IF(AND(MONTH($P2)=MONTH(R$1),YEAR($P2)=YEAR(R$1)),1,0)) copy down and across up to AC17.

Sum each column.

This formula works for ex. hire date 24/1/2011 and termination date 26/1/2011. The employee is counted for January 31, 2011.

 

[R Pelle]

One way, try these and copy down:
Q2: =--AND(O2<"1/1/2011"+0,P2>"1/1/2011"+0)
R2: =--AND(O2<"1/31/2011"+0,P2>"1/31/2011"+0)

If I'm not mistaken, this would not count CAUMO01 in January, whereas Njimack's solution does.

Rob

 

[R Pelle]

Euh njimack's solution falls over if Employee works from e.g. 2 jan 2011 to 31 jan 11. Employee will be counted in jan and feb.

I apologise for me bugging in but I too am looking for a similar way of resolving what the OP is trying.

 

[taigovinda]

Euh njimack's solution falls over if Employee works from e.g. 2 jan 2011 to 31 jan 11. Employee will be counted in jan and feb.

I apologise for me bugging in but I too am looking for a similar way of resolving what the OP is trying.

Based on the OP's request, njimack and I both created solutions that function precisely as we intended (can only speak for myself, but guessing here). I agree that they work differently, as pointed out in post #6 - and that they may or may not do what the OP intended...
If someone is looking for help to create a solution that functions differently than both, please post the desired *results* so that it is very clear what is the expectation.

Tai

 

[R Pelle]

The formula would need to include those who may have worked in the month and also been terminated in the same month hired.

Ultimately I need a Starting Headcount for the month and an Ending Headcount for the month.

Based on the OP's request, njimack and I both created solutions that function precisely as we intended...
Tai

Hi Tai, I agree. I believe I glanced over the second part as quoted above and only concentrated on the 1st part where the employee needed to be counted too if he only worked for part of the same month.

I've been playing around with njimack's solution and got it to work for me.
I report on resources too and count them as positive if they've worked a partial month.

Rob

 

[taigovinda]

Cool, glad you got it going. Obviously I glossed over the part of post #1 that didn't gel for me right away... Incidentally, I think MrVillareal probably posted what you want, in post #5. He counts a 3-day-long employee at the end of the month when they were hired/fired - *and* he explicitly says that's what he is doing, love it!