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

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

 

[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")))))