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)
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)
A | B | D | E | H | I | J | K | L | M | N | O | P | Q |
name | emp id | contract start date | contract end date | contract type | contract status | employee base | gender | status | FTE | theoretical working days | theoretical working hours | 01/01/2013 | 31/12/2013 |
Ellie | 00015 | 01/01/2013 | 31/12/2013 | employee | perm | London | Female | man | 1.0 | ||||
Davie | 00011 | 14/05/2013 | 31/12/2013 | employee | perm | London | male | sts | 0.6 | ||||
Sally | 00008 | 01/01/2013 | 31/12/2013 | employee | perm | Aberdeen | female | sts | 1.0 | ||||
Bill | 00020 | 14/06/2013 | 05/12/2013 | employee | perm | London | male | man | 0.8 | ||||
Frank | 00014 | 08/01/2013 | 31/12/2013 | employee | perm | Aberdeen | male | man | 1.0 |