Spreadsheet: 108926d1305842191-trying-to-get-a-weighted-monthly-figure-for-staff-attrition-fte-calculations-mod2
Sheet: Movements

Month | Name | Date Effective | End Date | FTE | FTE Impact | Weighted FTE in one calc | Adjusted Effective date | Adjusted End Date | Days Worked | Day Weighting | Weighted FTE
2011-03-01 00:00:00 | Name 1 | 2011-03-14 00:00:00 | 2011-09-09 00:00:00 | 1 | -1 | -0.5806451612903225 | 14 | 31 | 13 | 0.5806451612903225 | -0.5806451612903225
2011-03-01 00:00:00 | Name 2 | 2011-03-28 00:00:00 | 2011-04-29 00:00:00 | 1 | -1 | -0.1290322580645161 | 28 | 31 | 27 | 0.1290322580645161 | -0.1290322580645161
2011-03-01 00:00:00 | Name 3 | 2011-03-04 00:00:00 | 2012-03-15 00:00:00 | 1 | -1 | -0.9032258064516129 | 4 | 31 | 3 | 0.9032258064516129 | -0.9032258064516129
2011-03-01 00:00:00 | Name 4 | 2011-02-07 00:00:00 | 2011-03-11 00:00:00 | 1 | -1 | -0.8064516129032258 | 1 | 11 | 20 | 0.3548387096774194 | -0.3548387096774194
2011-03-01 00:00:00 | Name 5 | 2011-03-05 00:00:00 | n/a | 0.5 | -0.5 | -0.4354838709677419 | 5 | 31 | 4 | 0.8709677419354839 | -0.4354838709677419
2011-03-01 00:00:00 | Name 6 | 2011-03-02 00:00:00 | n/a | 1 | -1 | -0.967741935483871 | 2 | 31 | 1 | 0.967741935483871 | -0.967741935483871
2011-03-01 00:00:00 | Name 7 | 2011-03-05 00:00:00 | 2011-03-07 00:00:00 | 1 | -1 | -0.8709677419354839 | 5 | 7 | 28 | 0.09677419354838712 | -0.09677419354838712
2011-03-01 00:00:00 | Name 8 | 2011-03-29 00:00:00 | n/a | 1 | -1 | -0.09677419354838712 | 29 | 31 | 28 | 0.09677419354838712 | -0.09677419354838712
2011-03-01 00:00:00 | Name 9 | 2011-03-02 00:00:00 | n/a | 1 | -0.4 | -0.3870967741935484 | 2 | 31 | 1 | 0.967741935483871 | -0.3870967741935484
2011-03-01 00:00:00 | Name 10 | 2011-03-28 00:00:00 | n/a | 1 | -1 | -0.1290322580645161 | 28 | 31 | 27 | 0.1290322580645161 | -0.1290322580645161
2011-03-01 00:00:00 | Name 11 | 2011-03-21 00:00:00 | 2012-09-21 00:00:00 | 1 | -1 | -0.3548387096774194 | 21 | 31 | 20 | 0.3548387096774194 | -0.3548387096774194
2011-03-01 00:00:00 | Name 12 | 2011-03-21 00:00:00 | n/a | 1 | -1 | -0.3548387096774194 | 21 | 31 | 20 | 0.3548387096774194 | -0.3548387096774194
2011-03-01 00:00:00 | Name 13 | 2011-03-28 00:00:00 | n/a | 0.4 | -0.4 | -0.05161290322580645 | 28 | 31 | 27 | 0.1290322580645161 | -0.05161290322580645
2011-03-22 00:00:00 | Name 14 | 2011-03-28 00:00:00 | n/a | 1 | -1 | -0.1290322580645161 | 28 | 31 | 27 | 0.1290322580645161 | -0.1290322580645161
2011-03-22 00:00:00 | Name 15 | 2011-03-30 00:00:00 | n/a | 1 | -1 | -0.06451612903225812 | 30 | 31 | 29 | 0.06451612903225812 | -0.06451612903225812
 |  |  |  |  | -13.3 | -6.261290322580644 |  |  |  |  | -5.035483870967742
Issues:
Columns A-F are entered by our WFM team and must remain as they are as they are used for other purposes
Column in yellow is an attempt to display the weighted FTE (full time equivalent staff) that we lost throughout the month
i.e. if an agent leaves at the end of the month, it has much less weighting than an agent who leaves at the beginning of the month because we are down 1 person for a shorter period of just 1 day
Calculation in yellow works fine if the 'Effective' date is within the month listed in colunm A, and if the 'End' date is an actual date that is not in the same month as column A
I am running into problems trying to:
- get a calculation that converts the 'effective' date to the first day of month if it is in a previous month
- get a calculation that converts the 'end' date to the last day of month if it is in same month but a subsequent year (e.g. I6)
- check for errors in calculation caused by 'n/a' and replace these with last day of month
I HAVE PROVIDED ADDED FORMULA FOR THE MONTH END DATE TO CELLS (I4:I18)
I ALSO TESTED MONTH AND YEAR BETWEEN COLUMN D AND COLUMN A