| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create Range by joining multiple Ranges - passing multiple holidayranges to Workday function
Hi,
I am trying to use the workday function to pass it multiple Holiday ranges. =WORKDAY(F2,G2,INDIRECT({"Mhol","Nhol","Phol"})) where MHol, NHol, PHol are 3 defined names for 3 non-adjacent ranges. Please note this formula doesn't give me the correct result. So i want a formula to create a single range using the above ranges, then pass this joined range to the Workday function. i need a worksheet function and not VBA code. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create Range by joining multiple Ranges - passing multiple holidayranges to Workday function
On 21/03/2012 3:50 AM, noname wrote:
Hi, I am trying to use the workday function to pass it multiple Holiday ranges. =WORKDAY(F2,G2,INDIRECT({"Mhol","Nhol","Phol"})) where MHol, NHol, PHol are 3 defined names for 3 non-adjacent ranges. Please note this formula doesn't give me the correct result. So i want a formula to create a single range using the above ranges, then pass this joined range to the Workday function. i need a worksheet function and not VBA code. Hi I'm thinking you should be using the following: =NETWORKDAYS($F2,$G2)-(MHol+NHol+PHol) Where $F2 = Start Date & $G2 = End Date - the -Sum of Hols Applying the above to the month of March in over 31 days where there is 1 State(NHol) the total work days were Networdays(22) - Hols(1) = 21 Work Days. HTH Mick. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create Range by joining multiple Ranges - passing multipleholiday ranges to Workday function
On Wednesday, March 21, 2012 6:21:54 AM UTC+5:30, Vacuum Sealed wrote:
On 21/03/2012 3:50 AM, noname wrote: Hi, I am trying to use the workday function to pass it multiple Holiday ranges. =WORKDAY(F2,G2,INDIRECT({"Mhol","Nhol","Phol"})) where MHol, NHol, PHol are 3 defined names for 3 non-adjacent ranges. Please note this formula doesn't give me the correct result. So i want a formula to create a single range using the above ranges, then pass this joined range to the Workday function. i need a worksheet function and not VBA code. Hi I'm thinking you should be using the following: =NETWORKDAYS($F2,$G2)-(MHol+NHol+PHol) Where $F2 = Start Date & $G2 = End Date - the -Sum of Hols Applying the above to the month of March in over 31 days where there is 1 State(NHol) the total work days were Networdays(22) - Hols(1) = 21 Work Days. HTH Mick. Hi Mick, your formula results in a negative number. viz., =NETWORKDAYS($F2,$G2)-(Mhol+Nhol+PHol) = -152334 here are the Holiday lists: (P.S: these are in non-contagious columns.) as these are the holidays, the result should not be any date from these lists. MHOL NHOL PHOL 20-03-12 Tue 27-04-12 Fri 23-05-12 Wed 21-03-12 Wed 28-04-12 Sat 24-05-12 Thu 22-03-12 Thu 29-04-12 Sun 25-05-12 Fri 23-03-12 Fri 02-05-12 Wed 26-05-12 Sat 26-03-12 Mon 04-05-12 Fri 27-05-12 Sun 27-03-12 Tue 06-05-12 Sun 28-05-12 Mon 28-03-12 Wed 06-05-12 Sun 29-05-12 Tue 29-03-12 Thu 08-05-12 Tue 30-05-12 Wed 30-03-12 Fri 08-05-12 Tue 31-05-12 Thu 02-04-12 Mon 09-05-12 Wed 01-06-12 Fri 03-04-12 Tue 09-05-12 Wed 02-06-12 Sat 04-04-12 Wed 12-05-12 Sat 03-06-12 Sun 05-04-12 Thu 12-05-12 Sat 04-06-12 Mon 06-04-12 Fri 14-05-12 Mon 05-06-12 Tue 17-04-12 Tue 15-05-12 Tue 06-06-12 Wed 19-04-12 Thu 16-05-12 Wed 07-06-12 Thu 21-04-12 Sat 17-05-12 Thu 08-06-12 Fri 24-04-12 Tue 17-05-12 Thu 09-06-12 Sat 25-04-12 Wed 19-05-12 Sat 10-06-12 Sun 26-04-12 Thu 22-05-12 Tue 11-06-12 Mon |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create Range by joining multiple Ranges - passing multipleholiday ranges to Workday function
On Wednesday, March 21, 2012 4:21:07 PM UTC+5:30, noname wrote:
On Wednesday, March 21, 2012 6:21:54 AM UTC+5:30, Vacuum Sealed wrote: On 21/03/2012 3:50 AM, noname wrote: Hi, I am trying to use the workday function to pass it multiple Holiday ranges. =WORKDAY(F2,G2,INDIRECT({"Mhol","Nhol","Phol"})) where MHol, NHol, PHol are 3 defined names for 3 non-adjacent ranges. Please note this formula doesn't give me the correct result. So i want a formula to create a single range using the above ranges, then pass this joined range to the Workday function. i need a worksheet function and not VBA code. Hi I'm thinking you should be using the following: =NETWORKDAYS($F2,$G2)-(MHol+NHol+PHol) Where $F2 = Start Date & $G2 = End Date - the -Sum of Hols Applying the above to the month of March in over 31 days where there is 1 State(NHol) the total work days were Networdays(22) - Hols(1) = 21 Work Days. HTH Mick. Hi Mick, your formula results in a negative number. viz., =NETWORKDAYS($F2,$G2)-(Mhol+Nhol+PHol) = -152334 here are the Holiday lists: (P.S: these are in non-contagious columns.) as these are the holidays, the result should not be any date from these lists. MHOL NHOL PHOL 20-03-12 Tue 27-04-12 Fri 23-05-12 Wed 21-03-12 Wed 28-04-12 Sat 24-05-12 Thu 22-03-12 Thu 29-04-12 Sun 25-05-12 Fri 23-03-12 Fri 02-05-12 Wed 26-05-12 Sat 26-03-12 Mon 04-05-12 Fri 27-05-12 Sun 27-03-12 Tue 06-05-12 Sun 28-05-12 Mon 28-03-12 Wed 06-05-12 Sun 29-05-12 Tue 29-03-12 Thu 08-05-12 Tue 30-05-12 Wed 30-03-12 Fri 08-05-12 Tue 31-05-12 Thu 02-04-12 Mon 09-05-12 Wed 01-06-12 Fri 03-04-12 Tue 09-05-12 Wed 02-06-12 Sat 04-04-12 Wed 12-05-12 Sat 03-06-12 Sun 05-04-12 Thu 12-05-12 Sat 04-06-12 Mon 06-04-12 Fri 14-05-12 Mon 05-06-12 Tue 17-04-12 Tue 15-05-12 Tue 06-06-12 Wed 19-04-12 Thu 16-05-12 Wed 07-06-12 Thu 21-04-12 Sat 17-05-12 Thu 08-06-12 Fri 24-04-12 Tue 17-05-12 Thu 09-06-12 Sat 25-04-12 Wed 19-05-12 Sat 10-06-12 Sun 26-04-12 Thu 22-05-12 Tue 11-06-12 Mon Also, i dont have the enddate, just the startdate, no of days and holiday list. hence the need for the workday function. but the holiday list is non-adjacent ranges, so the need to club it into a single range (without using VBA ofcourse, as it needs to be macros free xlsx book). |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create Range by joining multiple Ranges - passing multiple holidayranges to Workday function
On 21/03/2012 10:19 PM, noname wrote:
MHOL NHOL PHOL 20-03-12 Tue 27-04-12 Fri 23-05-12 Wed 21-03-12 Wed 28-04-12 Sat 24-05-12 Thu 22-03-12 Thu 29-04-12 Sun 25-05-12 Fri 23-03-12 Fri 02-05-12 Wed 26-05-12 Sat 26-03-12 Mon 04-05-12 Fri 27-05-12 Sun 27-03-12 Tue 06-05-12 Sun 28-05-12 Mon 28-03-12 Wed 06-05-12 Sun 29-05-12 Tue 29-03-12 Thu 08-05-12 Tue 30-05-12 Wed 30-03-12 Fri 08-05-12 Tue 31-05-12 Thu 02-04-12 Mon 09-05-12 Wed 01-06-12 Fri 03-04-12 Tue 09-05-12 Wed 02-06-12 Sat 04-04-12 Wed 12-05-12 Sat 03-06-12 Sun 05-04-12 Thu 12-05-12 Sat 04-06-12 Mon 06-04-12 Fri 14-05-12 Mon 05-06-12 Tue 17-04-12 Tue 15-05-12 Tue 06-06-12 Wed 19-04-12 Thu 16-05-12 Wed 07-06-12 Thu 21-04-12 Sat 17-05-12 Thu 08-06-12 Fri 24-04-12 Tue 17-05-12 Thu 09-06-12 Sat 25-04-12 Wed 19-05-12 Sat 10-06-12 Sun 26-04-12 Thu 22-05-12 Tue 11-06-12 Mon Hi Can you breakdown the above list for me and explain how each of the *Hol apply as my assumption here is the MHol = Manual, NHol = National & PHol = Public although your array of dates above include weekend days and I would suggest there is may too many holidays for any one country, if so, where do you live so I can move there...:) Mick. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create Range by joining multiple Ranges - passing multipleholiday ranges to Workday function
On Wednesday, March 21, 2012 5:41:38 PM UTC+5:30, Vacuum Sealed wrote:
On 21/03/2012 10:19 PM, noname wrote: MHOL NHOL PHOL 20-03-12 Tue 27-04-12 Fri 23-05-12 Wed 21-03-12 Wed 28-04-12 Sat 24-05-12 Thu 22-03-12 Thu 29-04-12 Sun 25-05-12 Fri 23-03-12 Fri 02-05-12 Wed 26-05-12 Sat 26-03-12 Mon 04-05-12 Fri 27-05-12 Sun 27-03-12 Tue 06-05-12 Sun 28-05-12 Mon 28-03-12 Wed 06-05-12 Sun 29-05-12 Tue 29-03-12 Thu 08-05-12 Tue 30-05-12 Wed 30-03-12 Fri 08-05-12 Tue 31-05-12 Thu 02-04-12 Mon 09-05-12 Wed 01-06-12 Fri 03-04-12 Tue 09-05-12 Wed 02-06-12 Sat 04-04-12 Wed 12-05-12 Sat 03-06-12 Sun 05-04-12 Thu 12-05-12 Sat 04-06-12 Mon 06-04-12 Fri 14-05-12 Mon 05-06-12 Tue 17-04-12 Tue 15-05-12 Tue 06-06-12 Wed 19-04-12 Thu 16-05-12 Wed 07-06-12 Thu 21-04-12 Sat 17-05-12 Thu 08-06-12 Fri 24-04-12 Tue 17-05-12 Thu 09-06-12 Sat 25-04-12 Wed 19-05-12 Sat 10-06-12 Sun 26-04-12 Thu 22-05-12 Tue 11-06-12 Mon Hi Can you breakdown the above list for me and explain how each of the *Hol apply as my assumption here is the MHol = Manual, NHol = National & PHol = Public although your array of dates above include weekend days and I would suggest there is may too many holidays for any one country, if so, where do you live so I can move there...:) Mick. Ha ha :=) Mike, those were good guesses about the named ranges ;) no, these are just dummy dates for testing, which i input in 3 non-adjacent columns. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create Range by joining multiple Ranges - passing multipleholiday ranges to Workday function
On Wednesday, March 21, 2012 6:09:03 PM UTC+5:30, noname wrote:
On Wednesday, March 21, 2012 5:41:38 PM UTC+5:30, Vacuum Sealed wrote: On 21/03/2012 10:19 PM, noname wrote: MHOL NHOL PHOL 20-03-12 Tue 27-04-12 Fri 23-05-12 Wed 21-03-12 Wed 28-04-12 Sat 24-05-12 Thu 22-03-12 Thu 29-04-12 Sun 25-05-12 Fri 23-03-12 Fri 02-05-12 Wed 26-05-12 Sat 26-03-12 Mon 04-05-12 Fri 27-05-12 Sun 27-03-12 Tue 06-05-12 Sun 28-05-12 Mon 28-03-12 Wed 06-05-12 Sun 29-05-12 Tue 29-03-12 Thu 08-05-12 Tue 30-05-12 Wed 30-03-12 Fri 08-05-12 Tue 31-05-12 Thu 02-04-12 Mon 09-05-12 Wed 01-06-12 Fri 03-04-12 Tue 09-05-12 Wed 02-06-12 Sat 04-04-12 Wed 12-05-12 Sat 03-06-12 Sun 05-04-12 Thu 12-05-12 Sat 04-06-12 Mon 06-04-12 Fri 14-05-12 Mon 05-06-12 Tue 17-04-12 Tue 15-05-12 Tue 06-06-12 Wed 19-04-12 Thu 16-05-12 Wed 07-06-12 Thu 21-04-12 Sat 17-05-12 Thu 08-06-12 Fri 24-04-12 Tue 17-05-12 Thu 09-06-12 Sat 25-04-12 Wed 19-05-12 Sat 10-06-12 Sun 26-04-12 Thu 22-05-12 Tue 11-06-12 Mon Hi Can you breakdown the above list for me and explain how each of the *Hol apply as my assumption here is the MHol = Manual, NHol = National & PHol = Public although your array of dates above include weekend days and I would suggest there is may too many holidays for any one country, if so, where do you live so I can move there...:) Mick. Ha ha :=) Mike, those were good guesses about the named ranges ;) no, these are just dummy dates for testing, which i input in 3 non-adjacent columns. Here's a wonderful solution i found from Luke M :) =WORKDAY(H10,2,SMALL((Rng1,Rng2,Rng3),ROW(INDIRECT ("1:"&COUNT(Rng1,Rng2,Rng3))))) Cheers n thanks everyone :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| In WORKDAY function, how to exclude multiple/variable holiday rang | Excel Discussion (Misc queries) | |||
| Passing result of multiple split function results to 1 Array | Excel Programming | |||
| Can I create Multiple passwords to edit multiple ranges? | Excel Discussion (Misc queries) | |||
| Passing multiple strings to variable in Replace Function | Excel Programming | |||
| passing multiple strings to a function | Excel Programming | |||
Linear Mode
