Calculating Dates Using Different Values for NETWORKDAYS

[Kelly]

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]

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)

 

[Bob Phillips]

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]

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]

Kelly,

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[daddylonglegs]

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]

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

 

[daddylonglegs]

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]

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)

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}))))
nu
m_days)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"daddylonglegs"

wrote in message

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:

 

[Bob Phillips]

I think our posts crossed

Bob

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...