Dynamic Headcount Modeling

**bradharris2** · 09-11-2012, 07:42 PM

Looking to create a formula that automatically applies a 1, 0 or decimal based on the start and end date for a given month. My original formula applies a 1 or 0 and assumes either that resource started or ended a whole month. I would like this to be more dynamic calculating partial months for starting and ending months. E.g. if a person started 8/20/13 they would be assigned a 0.33 for the month of August 13' as they would be there for 10 days out of the average monthly days (365/12 = 30.4). Attachment below. Thank you for your help!
HC Formula v2.xlsx

**shg** · 09-11-2012, 08:37 PM

Please Login or Register  to view this content.

In C3 and copy across and down,

= MAX(0, MIN($B3, EDATE(C$2, 1)) - MAX(C$2, $A3)) / DAY(EOMONTH(C$2, 0))

**bradharris2** · 09-11-2012, 08:41 PM

Thanks shg, this looks great. Appreciate the help!

**edwincastellino** · 01-23-2013, 01:07 AM

Hi.. This is fantastic

is ther a way we can look at this from a broader horizon taking into consideration we have a number of staff where basically we have
> Staff ID / Name
> Date of Joining
> Date of Attrition
> Programme Name
Conditions are as follows.......
A staff is removed from the headcount only the following month and not the month he attrites
say we have 10 who joined C&G in Jan / 2 in Feb / 5 in March / 20 in April
But we have 5 attrited in Jan, hence the headcount in Jan should still show as 10 bu the headcount in Feb should show only 7 and March should show (7+5) and april should show (7+5+20).... and the programme name is also dynamic