Headcount counting by month

[jtbrooklyn]

Hi all,

I am trying to create a headcount file for forecasting in excel. I have a Date of Hire (DOH) and date of termination (DOT). The problem I have is with my "if" formula. Here is what I am trying to do:

If(DOH is < = to (date), DOT = "", DOT > (date), then (1) or "" ). The problem I have is with the DOT being blank and DOT being greater then (date) one is always true? Unless I have a DOT the cell is blank. So even if the termination date is in another month it will always come up blank?

 

[LxQ]

maybe something like this:

If(AND(DOH<TODAY(), OR(DOT>Today(), DOT=""),1,"")

 

[jtbrooklyn]

Thanks for the info but maybe if you see what kind of results I am looking for it may be easier to understand. Here is example: Everything would be driven off of either the DOH or the DOT.

[TABLE="width: 896"]
<colgroup><col style="width:48pt" span="14" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]DOH[/TD]
[TD="class: xl63, width: 64"] DOT
[/TD]
[TD="class: xl64, width: 64"]Jan[/TD]
[TD="class: xl64, width: 64"]Feb[/TD]
[TD="class: xl64, width: 64"]Mar[/TD]
[TD="class: xl64, width: 64"]Apr[/TD]
[TD="class: xl64, width: 64"]May[/TD]
[TD="class: xl64, width: 64"]Jun[/TD]
[TD="class: xl64, width: 64"]Jul[/TD]
[TD="class: xl64, width: 64"]Aug[/TD]
[TD="class: xl64, width: 64"]Sep[/TD]
[TD="class: xl64, width: 64"]Oct[/TD]
[TD="class: xl64, width: 64"]Nov[/TD]
[TD="class: xl64, width: 64"]Dec[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/2/2010[/TD]
[TD][/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]
[/TR]
[TR]
[TD="class: xl65, align: right"]2/1/2013[/TD]
[TD][/TD]
[TD][/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]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/2/2011[/TD]
[TD="class: xl65, align: right"]6/1/2013
[/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/3/2011[/TD]
[TD="class: xl65, align: right"]1/1/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[LxQ]

I would suggest that first you change the headings to dates, and format them with the "Mar-01" format... so where it says "Jan", change that to 01/01/13 and change the format to show "Jan-13"

Then, copy this in for the first formula:
=IF(AND($A2<=C$1,OR($B2="",$B2>C$1)),1,"")

Then copy the cell and paste it in the next cell. Excel will update the formula to fit the next cell.

 

[jtbrooklyn]

Perfect , I knew I was missing something. I was not adding the "or", thanks for the help!

 

[jtbrooklyn]

Okay so I am expanding the method of the file. So I have the first part working for "1" or "", Next I want to be able to calculate the networking days if someone is terminated half way through the month. Below is the out comes I am trying to get. I am having a problem with my networking days. Here is the formula i am using but again I am missing something =IF(OR(G4=1,B4="",OR(B4=H1)),H1,NETWORKDAYS(H2,B4))

		HC	NWD	Mthly Sal	HC	NWD

<colgroup><col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="width:48pt" span="3" width="64"> </colgroup><tbody>
[TD="class: xl70, width: 143, colspan: 2"]Working days[/TD]
[TD="class: xl71, width: 68"] [/TD]
[TD="class: xl71, width: 64"] [/TD]
[TD="class: xl71, width: 64, align: right"]23[/TD]
[TD="class: xl71, width: 68"] [/TD]
[TD="class: xl71, width: 64"] [/TD]
[TD="class: xl71, width: 64, align: right"]20[/TD]
[TD="class: xl72, width: 64"] [/TD]

[TD="class: xl69"] [/TD]

[TD="class: xl73"]Mthly Sal[/TD]

[TD="class: xl65"]DOH[/TD]
[TD="class: xl65"]DOT[/TD]
[TD="class: xl67"]Daily rate[/TD]
[TD="class: xl68"]Jan-13[/TD]
[TD="class: xl68"]Jan-13[/TD]
[TD="class: xl66"]Jan-13[/TD]
[TD="class: xl66"]Feb-13[/TD]
[TD="class: xl66"]Feb-13[/TD]
[TD="class: xl74"]Feb-13[/TD]

[TD="class: xl75, align: right"]1/1/2000[/TD]
[TD="class: xl76"][/TD]
[TD="class: xl77"]120.00[/TD]
[TD="class: xl70, align: right"]1[/TD]
[TD="class: xl71, align: right"]23[/TD]
[TD="class: xl72, align: right"]2760[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="class: xl73, align: right"]2400[/TD]

[TD="class: xl75, align: right"]11/1/2008[/TD]
[TD="class: xl76"]2/15/2013[/TD]
[TD="class: xl77"]250.00[/TD]
[TD="class: xl69, align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD="class: xl73, align: right"]5750[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="class: xl73, align: right"]2750[/TD]

[TD="class: xl75, align: right"]1/2/2011[/TD]
[TD="class: xl76"]1/24/2013[/TD]
[TD="class: xl77"]350.00[/TD]
[TD="class: xl69, align: right"]1[/TD]
[TD="align: right"]18[/TD]
[TD="class: xl73, align: right"]6300[/TD]

[TD="class: xl73"] [/TD]

[TD="class: xl78, align: right"]1/3/2011[/TD]
[TD="class: xl79"]11/1/2013[/TD]
[TD="class: xl80"]133.00[/TD]
[TD="class: xl81, align: right"]1[/TD]
[TD="class: xl82, align: right"]23[/TD]
[TD="class: xl83, align: right"]3059[/TD]
[TD="class: xl82, align: right"]1[/TD]
[TD="class: xl82, align: right"]20[/TD]
[TD="class: xl83, align: right"]2660[/TD]

</tbody>

 

[LxQ]

Not sure what your H2 is, it looks to me like "NWD" is in H2... so if you meant H3, then maybe think of this:
your max net working days are either the total working days, or they are cut short by a DOT..

then take the lesser of either the total working days, or the difference between DOT and the first of the month..
=MIN(H$1,NETWORKDAYS(H$3,B4,))

just in case this ever becomes negative, use 0 instead.. you can use:
=MAX(0,MIN(H$1,NETWORKDAYS(H$3,B4,)))

if you add =IF(OR(G4=1... then you eliminate the negative results, and you won't need the MAX formula..

 

[jtbrooklyn]

Thanks again for your help! I think that will work. Here is how the code ended up =IF(B3="",20,MAX(0,MIN(H$1,NETWORKDAYS(H$2,B3,)))), FYI NWD was a heading just for you I did not have it in my file.I added the "" part so that it gives me the MAX if there is no DOT for that month, make sense?

 

[LxQ]

sure... although I think you're adding unnecessary parts to the formula... what if H1 isn't 20? how will this work: =IF(B3="",20...?

also, I think the MIN(H$1,NETWORKDAYS.. takes the lesser of either the days between the two dates, or the 20 days or whatever else is in H1, in other words, the case when it should 20 as an answer is already covered..

 

[jtbrooklyn]

Got it, Thanks again