Prev: Row to Column
Next: Date calculation
From: XLNut on 1 Nov 2009 01:59
Is there a way to lookup a range of holidays based on a person's name, and
then use that range in the WORKDAY function? I have a list of employees and
their days off (holidays) and I'd like to use a different range of holidays
for each employee. Ideas?
--
XLNut
From: JP Ronse on 1 Nov 2009 07:12
Hi XLNut,

Perhaps following can bring you to some ideas to work it out further.

In the columns K:Z, I have in K the employees and in de columns beside their
days off.

This looks like:
K L M N O
P Q ...
JP 02/11/2009 03/11/2009 04/11/2009 05/11/2009
LO 05/11/2009 09/11/2009
PN 06/11/2009 12/11/2009 13/11/2009


In comlumn A, I have also the employees:
A B
JP
LO
PN


In B, I calculate their networkdays:
=NETWORKDAYS("1/11/2009";"30/11/2009";INDIRECT(ADDRESS(MATCH(A1;K:K;0);13;1)
& ":" & ADDRESS(MATCH(A1;K:K;0);26;1)))

Start date = November 1st
End date = last day of November

MATCH(A1;K:K;0);13;1) gives the row number of the employee in K:Z (K=column
11, Z = 26)
ADDRESS(MATCH(A1;K:K;0);12;1) => $L$1
ADDRESS(MATCH(A1;K:K;0);26;1)=> $Z$1
Indirect to pick up the full range.

In this example, the functions returns:
JP 17
LO 19
PN 18

I'm sure there are better ways but it is already a starting point.

Wkr,

JP



"XLNut" <XLNut(a)discussions.microsoft.com> wrote in message
news:41C21208-FFD6-4C00-B926-BBF4CDD9239A(a)microsoft.com...
> Is there a way to lookup a range of holidays based on a person's name, and
> then use that range in the WORKDAY function? I have a list of employees
> and
> their days off (holidays) and I'd like to use a different range of
> holidays
> for each employee. Ideas?
> --
> XLNut


From: barry houdini on 1 Nov 2009 09:00
On Nov 1, 12:12 pm, "JP Ronse" <fb893...(a)skynet.be> wrote:
> Hi XLNut,
>
> Perhaps following can bring you  to some ideas to work it out further.
>
> In the columns K:Z, I have in K the employees and in de columns beside their
> days off.
>
> This looks like:
> K                L                M                N               O
> P            Q ...
>       JP 02/11/2009 03/11/2009 04/11/2009 05/11/2009
>       LO 05/11/2009 09/11/2009
>       PN 06/11/2009 12/11/2009 13/11/2009
>
> In comlumn A, I have also the employees:
> A        B
>       JP
>       LO
>       PN
>
> In B, I calculate their networkdays:
> =NETWORKDAYS("1/11/2009";"30/11/2009";INDIRECT(ADDRESS(MATCH(A1;K:K;0);13;1­)
> & ":" & ADDRESS(MATCH(A1;K:K;0);26;1)))
>
> Start date = November 1st
> End date = last day of November
>
> MATCH(A1;K:K;0);13;1) gives the row number of the employee in K:Z (K=column
> 11, Z = 26)
> ADDRESS(MATCH(A1;K:K;0);12;1)  => $L$1
> ADDRESS(MATCH(A1;K:K;0);26;1)=> $Z$1
> Indirect to pick up the full range.
>
> In this example, the functions returns:
> JP          17
> LO         19
> PN         18
>
> I'm sure there are better ways but it is already a starting point.
>
> Wkr,
>
> JP
>
> "XLNut" <XL...(a)discussions.microsoft.com> wrote in message
>
> news:41C21208-FFD6-4C00-B926-BBF4CDD9239A(a)microsoft.com...
>
>
>
> > Is there a way to lookup a range of holidays based on a person's name, and
> > then use that range in the WORKDAY function? I have a list of employees
> > and
> > their days off (holidays) and I'd like to use a different range of
> > holidays
> > for each employee. Ideas?
> > --
> > XLNut- Hide quoted text -
>
> - Show quoted text -

Let's say you have employee names in row 1, e.g. H1:Z1.....and then
underneath you have the holidays listed for each down to row 20 then
you can use WORKDAY like this to add 10 days to a date in A2....for
employee shown in B2

=WORKDAY(A2,10,INDEX(H$2:Z$20,0,MATCH(B2,H$1:Z$1,0)))

regards, barry
From: JP Ronse on 1 Nov 2009 09:13
Hi Barry,

I knew for sure someone would find a better approach.

Wkr,

JP

"barry houdini" <barry.houdini(a)virgin.net> wrote in message
news:589ee1b1-50af-4658-aab2-b550731eeeda(a)m26g2000yqb.googlegroups.com...
On Nov 1, 12:12 pm, "JP Ronse" <fb893...(a)skynet.be> wrote:
> Hi XLNut,
>
> Perhaps following can bring you to some ideas to work it out further.
>
> In the columns K:Z, I have in K the employees and in de columns beside
> their
> days off.
>
> This looks like:
> K L M N O
> P Q ...
> JP 02/11/2009 03/11/2009 04/11/2009 05/11/2009
> LO 05/11/2009 09/11/2009
> PN 06/11/2009 12/11/2009 13/11/2009
>
> In comlumn A, I have also the employees:
> A B
> JP
> LO
> PN
>
> In B, I calculate their networkdays:
> =NETWORKDAYS("1/11/2009";"30/11/2009";INDIRECT(ADDRESS(MATCH(A1;K:K;0);13;1�)
> & ":" & ADDRESS(MATCH(A1;K:K;0);26;1)))
>
> Start date = November 1st
> End date = last day of November
>
> MATCH(A1;K:K;0);13;1) gives the row number of the employee in K:Z
> (K=column
> 11, Z = 26)
> ADDRESS(MATCH(A1;K:K;0);12;1) => $L$1
> ADDRESS(MATCH(A1;K:K;0);26;1)=> $Z$1
> Indirect to pick up the full range.
>
> In this example, the functions returns:
> JP 17
> LO 19
> PN 18
>
> I'm sure there are better ways but it is already a starting point.
>
> Wkr,
>
> JP
>
> "XLNut" <XL...(a)discussions.microsoft.com> wrote in message
>
> news:41C21208-FFD6-4C00-B926-BBF4CDD9239A(a)microsoft.com...
>
>
>
> > Is there a way to lookup a range of holidays based on a person's name,
> > and
> > then use that range in the WORKDAY function? I have a list of employees
> > and
> > their days off (holidays) and I'd like to use a different range of
> > holidays
> > for each employee. Ideas?
> > --
> > XLNut- Hide quoted text -
>
> - Show quoted text -

Let's say you have employee names in row 1, e.g. H1:Z1.....and then
underneath you have the holidays listed for each down to row 20 then
you can use WORKDAY like this to add 10 days to a date in A2....for
employee shown in B2

=WORKDAY(A2,10,INDEX(H$2:Z$20,0,MATCH(B2,H$1:Z$1,0)))

regards, barry


From: Gary''s Student on 1 Nov 2009 09:55
Say we have 4 employees:
Larry
More
Curley
Shep

We make a holiday table, in F1 thru I4, enter:

Larry Moe Curley Shep
1/13/2009 1/13/2009 1/13/2009
1/14/2009 1/14/2009
1/15/2009
Then we create some Defined Names:

Larry for: F2
Moe for: G2
Curley for: H2:H3
Shep for: I2 thru I4

The names can now be used as holiday tables. The following formulas:

=NETWORKDAYS("1/12/2009","1/16/2009")
=NETWORKDAYS("1/12/2009","1/16/2009",Larry)
=NETWORKDAYS("1/12/2009","1/16/2009",Moe)
=NETWORKDAYS("1/12/2009","1/16/2009",Curley)
=NETWORKDAYS("1/12/2009","1/16/2009",Shep)

will display:

5
5
4
3
2

You can even use the name as a variable. In A1 enter:
Curley
and then:
=NETWORKDAYS("1/12/2009","1/16/2009",INDIRECT(A1))
will also display 3
--
Gary''s Student - gsnu200908


"XLNut" wrote:

> Is there a way to lookup a range of holidays based on a person's name, and
> then use that range in the WORKDAY function? I have a list of employees and
> their days off (holidays) and I'd like to use a different range of holidays
> for each employee. Ideas?
> --
> XLNut
 | 
Pages: 1
Prev: Row to Column
Next: Date calculation