Calculating Dates Using Different Values for NETWORKDAYS

Kelly · 04-19-2006, 01:10 PM

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.

Bob Phillips · 04-19-2006, 01:30 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

Bob Phillips · 04-19-2006, 01:30 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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 · 04-19-2006, 01:50 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
>
>
>

Bob Phillips · 04-19-2006, 02:55 PM

Kelly,

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kelly" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > >
> >
> >
> >

**daddylonglegs** · 04-21-2006, 08:13 PM

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

Bob Phillips · 04-22-2006, 05:45 AM

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

**daddylonglegs** · 04-22-2006, 06:00 AM

I see what you mean, Bob. Perhaps it would be better to use

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

Bob Phillips · 04-22-2006, 06:10 AM

LOL. Or even

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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"
<[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

Bob Phillips · 04-22-2006, 10:15 AM

I think our posts crossed :-)

Bob

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> I see what you mean, Bob. Perhaps it would be better to use
>
>
=SMALL(IF(WEEKDAY(ROW(INDIRECT(A1&":"&A1+B1*5)))={4,6},ROW(INDIRECT(A1&":"&A
1+B1*5))),B1)
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=534245
>