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

[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.

 

[Vacuum Sealed]

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.

 

[noname]

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

 

[noname]

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

 

[Vacuum Sealed]

MHOL NHOL PHOL

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.

 

[noname]

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.

 

[noname]

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