Total Leave

[Tia]

Dear Sir,

I am working on annual leave and i want to have total days for 7 days
not networkdays excluding the weekend
i want the total number of days excluding the holidays

B18= Start Date
C18= End Date
Holidays ='Main'!B73:B100

What is the formula that i should use to count the total of days
execpt the holidays

Thank you in advance

 

[Bob Phillips]

=C18-B18+1-SUMPRODUCT(--(ISNUMBER(MATCH(Main!B73:B100,ROW(INDIRECT(C18&":"&B18)),0))))

 

[Tia]

=C18-B18+1-SUMPRODUCT(--(ISNUMBER(MATCH(Main!B73:B100,ROW(INDIRECT(C18&":"&­B18)),0))))

--
__________________________________
HTH

Bob










- Show quoted text -

Ok but when i scroll down even thought the B and the C are empty and
there is no date in them i get total of one so total of days taken is
not right
What can i do to add one only if there is date inside cell B and C and
if the cells is empty the total will appear as 0

Kindly advise

 

[Tia]

When you say total days except holidays do you mean all the days worked
monday to friday excluding any time taken off in that period?, if so






--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

What i meant is the following
I am working n UAE and the vacation calculation is counted with the
weekends wich means with friday and saturday only the holidays are
deducted from the total days

 

[Bob Phillips]

=IF(OR(B18="",C18=""),"",C18-B18+1-SUMPRODUCT(--(ISNUMBER(MATCH(Main!$B$73:$B$100,ROW(INDIRECT(C18&":"&­B18)),0)))))


--
__________________________________
HTH

Bob

=C18-B18+1-SUMPRODUCT(--(ISNUMBER(MATCH(Main!B73:B100,ROW(INDIRECT(C18&":"&­B18)),0))))

--
__________________________________
HTH

Bob










- Show quoted text -

Ok but when i scroll down even thought the B and the C are empty and
there is no date in them i get total of one so total of days taken is
not right
What can i do to add one only if there is date inside cell B and C and
if the cells is empty the total will appear as 0

Kindly advise