Total Leave
 

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

Total Leave
 

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

--
__________________________________
HTH

Bob

"Tia" wrote in message
...
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

Total Leave
 

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
what denotes the time taken off?Tia;183084 Wrote:
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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50589

Total Leave
 

On Jan 15, 12:56*pm, "Bob Phillips" wrote:
=C18-B18+1-SUMPRODUCT(--(ISNUMBER(MATCH(Main!B73:B100,ROW(INDIRECT(C18&":" &*B18)),0))))

--
__________________________________
HTH

Bob

"Tia" wrote in message

...



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- Hide quoted text -

- 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

Total Leave
 

On Jan 15, 1:15*pm, Simon Lloyd
wrote:
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
what denotes the time taken off?Tia;183084 Wrote:

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

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=50589

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

Total Leave
 

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

If you do join please make your posts in this thread
http://tinyurl.com/83gp6e so that people that have been helping you or
following this thread can continue to do so.
Tia;183185 Wrote:
On Jan 15, 1:15*pm, Simon Lloyd
wrote:
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
what denotes the time taken off?Tia;183084 Wrote:

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

--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile:'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread:'Total Leave - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=50589)

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

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50589

Total Leave
 

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


--
__________________________________
HTH

Bob

"Tia" wrote in message
...
On Jan 15, 12:56 pm, "Bob Phillips" wrote:
=C18-B18+1-SUMPRODUCT(--(ISNUMBER(MATCH(Main!B73:B100,ROW(INDIRECT(C18&":" &*B18)),0))))

--
__________________________________
HTH

Bob

"Tia" wrote in message

...



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- Hide quoted text -

- 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