IF nested Complex formula Excel 2010

[HelenL]

Excel 2010
Hi, any can anyone help with this please? Thank you!

Excel 2010
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 or Scotland. Then multiplied by FTE as some are part time. 8 hr working day.

So far ....
=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

01/01/2013

31/12/2013

Name

employee ID

Start Date

Contract End Date

Contract Type

Contract Status

Employee Base Location

Gender

Status

FTE

Theoretical Working Days

Theoretical Working Hours

Ellie

00015

01/01/2013

31/12/2013

Employee

Perm

London

Female

MAN

1.0

David

00011

14/05/2013

31/12/2013

Employee

perm

London

Male

STJ

0.6

Sally

00008

01/01/2013

31/12/2013

Employee

perm

Aberdeen

Female

STJ

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

<tbody>
</tbody>

 

[ramohse]

Does this all have to be in one cell? In other words, can you put this in a table, break the formula up into pieces in separate columns, then just hide the "calculation" columns for presentation purposes?

 

[HelenL]

Hoping to come up with a one cell calculation if possible, please

 

[ramohse]

If I may ask, why are you starting the IF() function with whether or not it is 2013? If all of this takes place in 2013 (or you have the bank holidays for periods of time outside of 2013) you can just input the start and end dates and Excel will calculate the work days.

I got the below to work for me, assuming that there were a different number of holidays for London and Aberdeen.

=IF(J2="London",(NETWORKDAYS.INTL(D2,E2,1,'Bank holidays'!$A$2:$A$7)*M2),IF(J2="Aberdeen",(NETWORKDAYS.INTL(D2,E2,1,'Bank holidays'!$A$2:$A$6)*M2),""))