Calculating Dates Using Different Values for NETWORKDAYS

Hello -

I have employees that have different number of business days they work. I
need to be able to calculate when the employee has utilized a specific number
of business days specific to the days of the week they work and the number of
days per week work. For example, if I have an employee that works 3 business
days per week (Specifically M, W, and F), and I need to know the date this
employee worked a total of 30 business days, is there a way to calcuate this
date (which should be 6/9/06 if we use a start date of 4/3/06.

This caters for holidays as well

=start_date+SIGN(B1)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(ROW(INDIRECT("
1:"&ABS(days)*10))))={1,2,3})*
ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))),holidays
,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kelly" <Kelly@discussions.microsoft.com> wrote in message
news:9311E87A-5E10-425C-BD0B-B83CD633D95E@microsoft.com...
> Hello -
>
> I have employees that have different number of business days they work. I
> need to be able to calculate when the employee has utilized a specific
number
> of business days specific to the days of the week they work and the number
of
> days per week work. For example, if I have an employee that works 3
business
> days per week (Specifically M, W, and F), and I need to know the date this
> employee worked a total of 30 business days, is there a way to calcuate
this
> date (which should be 6/9/06 if we use a start date of 4/3/06.
>
>

Hi Bob -

The business I am does not cater to holidays. They are considered business
days as well. Would I just take out the ",holidays, 0))" portion of the
equation. This seems to be way over my head as I am not that familiar wtih
formulas.

"Bob Phillips" wrote:

> This caters for holidays as well
>
> =start_date+SIGN(B1)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(ROW(INDIRECT("
> 1:"&ABS(days)*10))))={1,2,3})*
> ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))),holidays
> ,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Kelly" <Kelly@discussions.microsoft.com> wrote in message
> news:9311E87A-5E10-425C-BD0B-B83CD633D95E@microsoft.com...
> > Hello -
> >
> > I have employees that have different number of business days they work. I
> > need to be able to calculate when the employee has utilized a specific
> number
> > of business days specific to the days of the week they work and the number
> of
> > days per week work. For example, if I have an employee that works 3
> business
> > days per week (Specifically M, W, and F), and I need to know the date this
> > employee worked a total of 30 business days, is there a way to calcuate
> this
> > date (which should be 6/9/06 if we use a start date of 4/3/06.
> >
> >
>
>
>

Kelly,

Just create a range name called holidays, but leave it blank.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kelly" <Kelly@discussions.microsoft.com> wrote in message
news:377F4D3A-3EB3-4E0C-B267-D85BDF7542B7@microsoft.com...
> Hi Bob -
>
> The business I am does not cater to holidays. They are considered
business
> days as well. Would I just take out the ",holidays, 0))" portion of the
> equation. This seems to be way over my head as I am not that familiar
wtih
> formulas.
>
> "Bob Phillips" wrote:
>
> > This caters for holidays as well
> >
> >
=start_date+SIGN(B1)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(ROW(INDIRECT("
> > 1:"&ABS(days)*10))))={1,2,3})*
> >
ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))),holidays
> > ,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Kelly" <Kelly@discussions.microsoft.com> wrote in message
> > news:9311E87A-5E10-425C-BD0B-B83CD633D95E@microsoft.com...
> > > Hello -
> > >
> > > I have employees that have different number of business days they
work. I
> > > need to be able to calculate when the employee has utilized a specific
> > number
> > > of business days specific to the days of the week they work and the
number
> > of
> > > days per week work. For example, if I have an employee that works 3
> > business
> > > days per week (Specifically M, W, and F), and I need to know the date
this
> > > employee worked a total of 30 business days, is there a way to
calcuate
> > this
> > > date (which should be 6/9/06 if we use a start date of 4/3/06.
> > >
> > >
> >
> >
> >

If start date is in A1 and positive number of business days in B1

=SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*3))-1)={2,4,6},A1+ROW(INDIRECT("1:"&B1*3))-1),B1)

confirmed with CTRL+SHIFT+ENTER

note that {2,4,6} refers to Mon, Wed and Fri. Adjust accordingly for other combinations

It's a good formula, but it falls down if you go for just 2 days say
{2,4}because ROW(INDIRECT("1:"&B1*3)) creates a too small a comparison date
range.

You can allow for it, but it's a bit messy IMO

=SMALL(IF(WEEKDAY(start_date+ROW(INDIRECT("1:"&num_days*(6-COUNT({2,4,6}))))
-1)={2,4,6},start_date+ROW(INDIRECT("1:"&num_days*(6-COUNT({2,4,6}))))-1),nu
m_days)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"daddylonglegs" <daddylonglegs.26n32y_1145664902.5454@excelforum-nospam.com>
wrote in message
news:daddylonglegs.26n32y_1145664902.5454@excelforum-nospam.com...
>
> If start date is in A1 and positive number of business days in B1
>
>
=SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*3))-1)={2,4,6},A1+ROW(INDIRECT("1:
"&B1*3))-1),B1)
>
> confirmed with CTRL+SHIFT+ENTER
>
> note that {2,4,6} refers to Mon, Wed and Fri. Adjust accordingly for
> other combinations
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=534245
>

Sorry, meant

=start_date+SIGN(B1)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(ROW(INDIRECT("
1:"&ABS(days)*10))))={2,4,6})*
ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))),holidays
,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kelly" <Kelly@discussions.microsoft.com> wrote in message
news:9311E87A-5E10-425C-BD0B-B83CD633D95E@microsoft.com...
> Hello -
>
> I have employees that have different number of business days they work. I
> need to be able to calculate when the employee has utilized a specific
number
> of business days specific to the days of the week they work and the number
of
> days per week work. For example, if I have an employee that works 3
business
> days per week (Specifically M, W, and F), and I need to know the date this
> employee worked a total of 30 business days, is there a way to calcuate
this
> date (which should be 6/9/06 if we use a start date of 4/3/06.
>
>