Help calculate contractor costs by months or quarters

[Des66]

Hi. I'm trying to write a formula to calculate the cost of various employees spread over months (or quarters) so I can create graphs of the annual cost impact to a business. They all have different contract start and end dates along with varying daily rates of pay. Can anyone help me please? If a formula is not the best approach then please suggest an alternative as I'm stumped after being told I need to assume 20 working days per month so can't use networkdays!

Example below shows the expected results which were done manually for visualisation but obviously I don't want to trawl through 1000s of rows of data manually.... Thanks in advance

Contractor	Day Rate	Start Date	End Date	01-Jan-18	01-Feb-18	01-Mar-18	01-Apr-18	01-May-18	01-Jun-18	01-Jul-18	01-Aug-18	01-Sep-18	01-Oct-18
Bob	£225	01/02/2018	15/03/2018	£0	£4,500	£2,475	£0
Anne	£575	05/02/2018	20/04/2018	£0	£10,350	£11,500	£8,625
Chris	£300	10/01/2018	11/06/2019	£4,800	£6,000	£6,000	£6,000
Tim	£480	10/03/2018	26/11/2018	£0	£0	£9,600	£9,600
Jerry	£650	01/04/2018	02/08/2018	£0	£0	£0	£13,000
Kate	£200	01/07/2015	30/06/2019	£4,000	£4,000	£4,000	£4,000
Jack	£350	18/04/2017	25/03/2019	£7,000	£7,000	£7,000	£7,000
Mary	£295	31/01/2017	19/06/2018	£5,900	£5,900	£5,900	£5,900
Pete	£470	17/09/2018	29/06/2019	£0	£0	£0	£9,400
Susan	£380	22/07/2018	29/01/2019	£0	£0	£0	£7,600

<tbody>
</tbody>

 

[etaf]

I think this will work
BUT your Pete and Susan looks wrong
and Tim - starts in March , but you have 9,600

=IF(AND($C2>E$1,$D2<EOMONTH(E$1,0)),($D2-$C2)*$B2,IF(OR($C2>EOMONTH(E$1,0),$D2<=E$1),0,IF(AND($C2<=E$1,$D2>=EOMONTH(E$1,0)),20*$B2,IF(AND($D2<EOMONTH(E$1,0),$D2>=E$1),NETWORKDAYS(E$1,$D2)*$B2,IF(AND($D2>=EOMONTH(E$1,0),$C2>=E$1),NETWORKDAYS($C2,EOMONTH(E$1,0))*$B2,"tba")))))

 

[etaf]

sorry that has been changed by the forum

=IF(AND($C2 > E$1,$D2 < EOMONTH(E$1,0)),NETWORKDAYS($C2,$D2)*$B2,IF(OR($C2 > EOMONTH(E$1,0),$D2 < = E$1),0,IF(AND($C2 < =E$1,$D2 > = EOMONTH(E$1,0)),20*$B2,IF(AND($D2 < EOMONTH(E$1,0),$D2 > = E$1),NETWORKDAYS(E$1,$D2)*$B2,IF(AND($D2 > = EOMONTH(E$1,0),$C2 > = E$1),NETWORKDAYS($C2,EOMONTH(E$1,0))*$B2,"tba")))))

there may be an issue with networkdays and if part of a month
ie 2/jan to 31/jan = 21 Not 20

 

[etaf]

i have added a floor to stop 2/1/18 to 30/1/18 more than 20 days

=IF(AND($C2 > = E$1,$D2 < = EOMONTH(E$1,0)),FLOOR(NETWORKDAYS($C2,$D2),20)*$B2,IF(OR($C2 > EOMONTH(E$1,0),$D2 < = E$1),0,IF(AND($C2 < = E$1,$D2 > = EOMONTH(E$1,0)),20*$B2,IF(AND($D2 < EOMONTH(E$1,0),$D2 > = E$1),NETWORKDAYS(E$1,$D2)*$B2,IF(AND($D2 > = EOMONTH(E$1,0),$C2 > = E$1),NETWORKDAYS($C2,EOMONTH(E$1,0))*$B2,"tba")))))