| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
| Excel 2002 : Any single button to get sub total and grand total ? | Excel Discussion (Misc queries) | |||
| Leave Log | Excel Discussion (Misc queries) | |||
| calculate count on 2006 total, 2005 total, etc... | Excel Worksheet Functions | |||
| Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions | |||
![Tia[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
