Resource calculation- HELP PLEASE

[kads]

Hi Guys,

hope everyone is well.

I am trying to work out how much it would cost for a person who works variable days per week based on their start date and end dates and working days (add in any personal holidays, based on financial calender month.


I currently have the formula below:
=$M4*($N4/5)*(MAX(0,NETWORKDAYS(MAX(DI$3,$Q4),MIN(DATE(YEAR(DL$3),MONTH(DL$3)+1,0),$R4),publichols)-SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($Q4&":"&$R4)),{42000},0)))))

M4 = Day rate (can vary)
N4 = how many days per week they work (can vary)
DI3 = Financial month start (changes month to month, not calendar month)
q4 = resource start date (changes resource to resource)
DL = financial month end (changes month to month , not calendar month)
r4 = resource end date (changes resource to resource)
publichols = named cells listing all UK bank holidays (listed in seperate sheet)
{42000} = any personal holidays (serial number, can be many or none)

Any help welcome

thanks
Kads

 

[kads]

Anybody?
I want to work out how much does it cost per resource, per month, based on how many working days there are between 2 dates, any holidays, any bank holidays and how many days a week they work....

 

[kads]

Hey gurus, just bumping this back up for help...