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.

Calculating Dates Using Different Values for NETWORKDAYS
 

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" wrote in message
...
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.

Calculating Dates Using Different Values for NETWORKDAYS
 

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" wrote in message
...
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.

Calculating Dates Using Different Values for NETWORKDAYS
 

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" wrote in message
...
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.

Calculating Dates Using Different Values for NETWORKDAYS
 

Kelly,

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kelly" wrote in message
...
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" wrote in message
...
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.

Calculating Dates Using Different Values for NETWORKDAYS
 

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

Calculating Dates Using Different Values for NETWORKDAYS
 

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"
wrote in message
news:daddylonglegs.26n32y_1145664902.5454@excelfor um-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

Calculating Dates Using Different Values for NETWORKDAYS
 

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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=534245

Calculating Dates Using Different Values for NETWORKDAYS
 

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" wrote in message
...
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"

wrote in message
news:daddylonglegs.26n32y_1145664902.5454@excelfor um-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

Calculating Dates Using Different Values for NETWORKDAYS
 

I think our posts crossed :-)

Bob

"daddylonglegs"
wrote in message
news:daddylonglegs.26numm_1145700602.2883@excelfor um-nospam.com...

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