Create Range by joining multiple Ranges - passing multiple holidayranges to Workday function

  • Thread starter Thread starter noname
  • Start date Start date
N

noname

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 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.
Click to expand...
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

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.
Click to expand...

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
 
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
Click to expand...

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).
 
MHOL NHOL PHOL
Click to expand...

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.
 
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.
Click to expand...

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.
 
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.
Click to expand...

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 :)
 
Back
Top