Networkdays (working hours formula now to include holidays)
 

I am using a formula
=((NETWORKDAYS(C2,I2)-1)*(Z$2-Y$2)+IF(NETWORKDAYS(I2,I2),MEDIAN(MOD(I2,1),Z$2,Y$ 2),Z$2)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),Z$2,Y$2))

this gives me working hours Mon-Fri (Y and Z = start and finish times)

now I need to incorporate bank holidays please can you advise what i need to
do to the above formula
--
nigeo

Networkdays (working hours formula now to include holidays)
 

Help on Networkdays has your answer. You create a range of your holidays,
then tell the function where it is, as in:
=((Networkdays(c2,i2,x1:x10)...

Regards, Fred

"nigeo" wrote in message
...
I am using a formula
=((NETWORKDAYS(C2,I2)-1)*(Z$2-Y$2)+IF(NETWORKDAYS(I2,I2),MEDIAN(MOD(I2,1),Z$2,Y$ 2),Z$2)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),Z$2,Y$2))

this gives me working hours Mon-Fri (Y and Z = start and finish times)

now I need to incorporate bank holidays please can you advise what i need
to
do to the above formula
--
nigeo

Networkdays (working hours formula now to include holidays)
 

"nigeo" wrote in message
...
I am using a formula
=((NETWORKDAYS(C2,I2)-1)*(Z$2-Y$2)+IF(NETWORKDAYS(I2,I2),MEDIAN(MOD(I2,1),Z$2,Y$ 2),Z$2)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),Z$2,Y$2))

this gives me working hours Mon-Fri (Y and Z = start and finish times)

now I need to incorporate bank holidays please can you advise what i need
to
do to the above formula

Networkdays has a third, and optional, part to its syntax:

NETWORKDAYS(start_date,end_date,holidays)

Simply enter the dates of bank holidays in an area on the spreadsheet - say
A1:A12 and then add this to your formula.

HTH

V

Networkdays (working hours formula now to include holidays)
 

Hi,

NETWORKDAYS(StartDate,EndDate,Holidays)

Holidays being a range listing all the holidays as dates.

However, I don't follow how this formula does what you say you want. I
might be more useful if you showed us a sample of the data and told us what
you wanted from it. For example NETWORKDAYS(C2,C2) will always return 1 if
there is anything in C2, so why not just substitue 1? Or if necessary
IF(C2,1).

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"nigeo" wrote:

I am using a formula
=((NETWORKDAYS(C2,I2)-1)*(Z$2-Y$2)+IF(NETWORKDAYS(I2,I2),MEDIAN(MOD(I2,1),Z$2,Y$ 2),Z$2)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),Z$2,Y$2))

this gives me working hours Mon-Fri (Y and Z = start and finish times)

now I need to incorporate bank holidays please can you advise what i need to
do to the above formula
--
nigeo

Networkdays (working hours formula now to include holidays)
 

Sorry Shane the formula does give me what i want, not sure why, was given
formula on this discussion site, I am now sorted with the holiday thing as
well.

Regards
--
nigeo


"Shane Devenshire" wrote:

Hi,

NETWORKDAYS(StartDate,EndDate,Holidays)

Holidays being a range listing all the holidays as dates.

However, I don't follow how this formula does what you say you want. I
might be more useful if you showed us a sample of the data and told us what
you wanted from it. For example NETWORKDAYS(C2,C2) will always return 1 if
there is anything in C2, so why not just substitue 1? Or if necessary
IF(C2,1).

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"nigeo" wrote:

I am using a formula
=((NETWORKDAYS(C2,I2)-1)*(Z$2-Y$2)+IF(NETWORKDAYS(I2,I2),MEDIAN(MOD(I2,1),Z$2,Y$ 2),Z$2)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),Z$2,Y$2))

this gives me working hours Mon-Fri (Y and Z = start and finish times)

now I need to incorporate bank holidays please can you advise what i need to
do to the above formula
--
nigeo