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))))

 

[Simon Lloyd]

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

Dear Sir

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

B18= Start Dat
C18= End Dat
Holidays ='Main'!B73:B10

What is the formula that i should use to count the total of day
execpt the holiday

Thank you in advanc

--
Simon Lloy

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

 

[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

 

[Simon Lloyd]

It is difficult to visualise what you mean!, if you wish you can joi
the forum below completely free where you can attach a workbook to you
post so we can see what you are trying to achieve

If you do join please make your posts in this threa
http://tinyurl.com/83gp6e so that people that have been helping you o
following this thread can continue to do so

On Jan 15, 1:15*pm, Simon Lloyd <[email protected]
wrote


Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1
(http://www.thecodecage.com/forumz/showthread.php?t=50589

What i meant is the followin
I am working n UAE and the vacation calculation is counted with th
weekends wich means with friday and saturday only the holidays ar
deducted from the total day

--
Simon Lloy

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

 

[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