Excel 2010 IF nested date formula Help needed please

Helenl

New member
Joined
Sep 22, 2013
Messages
2
Reaction score
0
Points
0
Excel 2010 complex IF, or another solution ? Thanks for looking. Sample table below.

I need to calculate theoretical number of available working days and hours
during contract period , total which varies according to the start and finish
date, but all within the period 01/01/13 to 31/12/13, less the bank holidays,
total of which does vary whether based London(8) or Scotland (5). Then multiplied by
FTE as some are part time. 8 hr working day.

So far .... but can't cater for 2 sets of banks holidays, or a different start and end to contract within the period.

=IF(D2="","",IF(YEAR(D2)=2013,NETWORKDAYS.INTL(P1,$Q$1,1,'Bank
holidays'!$A$2:$A$9)*M2,NETWORKDAYS.INTL($D$2,$E$2,1,'Bank
holidays'!$A$2:$A$9)*M2)
ABDEHIJKLMNOPQ
nameemp idcontract start datecontract end datecontract typecontract statusemployee basegenderstatusFTEtheoretical working daystheoretical working hours01/01/201331/12/2013
Ellie0001501/01/201331/12/2013employeepermLondonFemaleman1.0
Davie0001114/05/201331/12/2013employeepermLondonmalests0.6
Sally0000801/01/201331/12/2013employeepermAberdeenfemalests1.0
Bill0002014/06/201305/12/2013employeepermLondonmaleman0.8
Frank0001408/01/201331/12/2013employeepermAberdeenmaleman1.0

 
Top