Labour Analysis Headcount

Dear All,

I've attached a file to demonstrate what I mean.

The formula should be cover the following four scenarios:

1. if start month and end month are both blank, the F.T.E should remain the same for the entire financial year (equal to the value on column F).

2. If start month is Apr, the F.T.E will start from Apr till the year end at July. The months prior to the start month should be having zero F.T.E

3. If start and end month is Apr and Nov respectively, the F.T.E remain the same between Apr and Nov. The rest of the months should be zero

4. If start month is blank and end month starts from Apr, all the moths prior to Apr (including Apr) should equal to the F.T.E. From Apr onwards, the F.T.E should turn zero.

I've manually demonstrated the result on the spreadsheet as the examples mentioned above. Can you help to convert this into excel formulas please?

Thank you so much.

Try the following formula:

=IF(AND($D2="",$E2=""),$F2,IF(COUNTIF($G$1:G$1,$E2)>0,0,IF(COUNTIF(H$1:$R$1,$D2)>0,0,$F2)))

Ok, minor tweak:

=IF(AND($D2="",$E2=""),$F2,IF(COUNTIF(F$1:$G$1,$E2)>0,0,IF(COUNTIF(H$1:$R$1,$D2)>0,0,$F2)))

In terms of constructing the formula, it's just a case of applying the logic.

First, if there is no start date and no end date, keep the FTE value (F2) the whole way through:
IF(AND($D2="",$E2=""),$F2
Otherwise, if the end date is in the past, the FTE drops to zero
IF(COUNTIF(F$1:$G$1,$E2)>0,0
And if the start date is in the future, the FTE is also zero
IF(COUNTIF(H$1:$R$1,$D2)>0,0

And if none of those situations occur, we populate with the FTE value.

I hope that makes sense?

Also, just to point out, the formula would be much simpler if you used numeric values instead of months - i.e. 1,2, etc, or dates - as they are much easier to compare than text.