Formula Workdays-Holidays-adjustments
 

I am trying to get my Workdays and Networkdays formulas to work right. I am
measuring time in number of days, and calculating due dates and number of
workdays for cycles. I do not want to include weekends, holidays, or
adjustment number of days. I have set up a separate worksheet and entered our
holidays and named the column €œHolidays€� but the formula is not working when
I add that section to it. I dont actually have the holidays in the formula
because it is not working yet.

=IF(OR($L5="",$L5="NOT RECEIVED"),"",WORKDAY(Q5,U$1,Holidays)-Adj days)
=If part not received yet, then do nothing, else calculate the due date for
this cycle by adding the number of workdays allowed to the due date of the
last cycle, and do not include days listed in €œHolidays€� and subtract number
days for adjustment.

Do these actually give me only workdays?
WORKDAY(Q5,U$1,Holidays)-Adj days
=IF(BN4="","",NETWORKDAYS(L4,BN4,Holidays)-BU4) (This one is suppose to
count the total workdays use on entire project listed on row.)

This is the basic set up:
the number of
workdays allowed
for the cycle 10 22
Start Date Adjustments cycle 1 Due Date Repeats for cycles
Entered as # activities formula until
project done.

11/20/05 2 12/1/05 formula

Formula Workdays-Holidays-adjustments
 

Glenna,

On the face of it, that looks okay.

What data do you have in L5, Q5, and U1, and what do you get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Glenna" wrote in message
...
I am trying to get my Workdays and Networkdays formulas to work right. I
am
measuring time in number of days, and calculating due dates and number of
workdays for cycles. I do not want to include weekends, holidays, or
adjustment number of days. I have set up a separate worksheet and entered
our
holidays and named the column "Holidays" but the formula is not working
when
I add that section to it. I don't actually have the holidays in the
formula
because it is not working yet.

=IF(OR($L5="",$L5="NOT RECEIVED"),"",WORKDAY(Q5,U$1,Holidays)-Adj days)
=If part not received yet, then do nothing, else calculate the due date
for
this cycle by adding the number of workdays allowed to the due date of the
last cycle, and do not include days listed in "Holidays" and subtract
number
days for adjustment.

Do these actually give me only workdays?
WORKDAY(Q5,U$1,Holidays)-Adj days
=IF(BN4="","",NETWORKDAYS(L4,BN4,Holidays)-BU4) (This one is suppose to
count the total workdays use on entire project listed on row.)

This is the basic set up:
the number of
workdays allowed
for the cycle 10 22
Start Date Adjustments cycle 1 Due Date Repeats for cycles
Entered as # activities formula until
project done.

11/20/05 2 12/1/05 formula

Formula Workdays-Holidays-adjustments
 

Bob,
For formula in cell U5: =IF(OR($L5="",$L5="NOT RECEIVED"),"",WORKDAY(Q5,U$1))
Column L is the start date of the project. Row 5 is one project.
Column Q contains the due date of previous cycle.
U$1 contain the number of workdays to add to due date of previous cycle for
the due date of the next cycle.

I do not have holidays added in yet because it is not working. I have been
MANUALLY adjusting. There is getting to be too many manual adjustments.

My concern is also when I just add the minus adjustments after
WORKDAY(Q5,U$1) if those are actually substracting workdays or calendar days.

"Bob Phillips" wrote:

Glenna,

On the face of it, that looks okay.

What data do you have in L5, Q5, and U1, and what do you get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Glenna" wrote in message
...
I am trying to get my Workdays and Networkdays formulas to work right. I
am
measuring time in number of days, and calculating due dates and number of
workdays for cycles. I do not want to include weekends, holidays, or
adjustment number of days. I have set up a separate worksheet and entered
our
holidays and named the column "Holidays" but the formula is not working
when
I add that section to it. I don't actually have the holidays in the
formula
because it is not working yet.

=IF(OR($L5="",$L5="NOT RECEIVED"),"",WORKDAY(Q5,U$1,Holidays)-Adj days)
=If part not received yet, then do nothing, else calculate the due date
for
this cycle by adding the number of workdays allowed to the due date of the
last cycle, and do not include days listed in "Holidays" and subtract
number
days for adjustment.

Do these actually give me only workdays?
WORKDAY(Q5,U$1,Holidays)-Adj days
=IF(BN4="","",NETWORKDAYS(L4,BN4,Holidays)-BU4) (This one is suppose to
count the total workdays use on entire project listed on row.)

This is the basic set up:
the number of
workdays allowed
for the cycle 10 22
Start Date Adjustments cycle 1 Due Date Repeats for cycles
Entered as # activities formula until
project done.

11/20/05 2 12/1/05 formula

Formula Workdays-Holidays-adjustments
 

Glenna,

I still cannot see why you are getting a problem.

To answer one specific question, if you subtract ADj Days as you show, it
will subtract calendar days, not workdays. To overcome this, you could
subtract ADj Days from the between cycle day amount, i.e. U$1-AdjDays

I have created a simple sample worksheet that shows all of that stuff
working fine, holidays, adjusted dates. Hopefully that will help. You can
get it at http://cjoint.com/?lqxVqdgY0n

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Glenna" wrote in message
...
Bob,
For formula in cell U5: =IF(OR($L5="",$L5="NOT
RECEIVED"),"",WORKDAY(Q5,U$1))
Column L is the start date of the project. Row 5 is one project.
Column Q contains the due date of previous cycle.
U$1 contain the number of workdays to add to due date of previous cycle
for
the due date of the next cycle.

I do not have holidays added in yet because it is not working. I have been
MANUALLY adjusting. There is getting to be too many manual adjustments.

My concern is also when I just add the minus adjustments after
WORKDAY(Q5,U$1) if those are actually substracting workdays or calendar
days.

"Bob Phillips" wrote:

Glenna,

On the face of it, that looks okay.

What data do you have in L5, Q5, and U1, and what do you get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Glenna" wrote in message
...
I am trying to get my Workdays and Networkdays formulas to work right.
I
am
measuring time in number of days, and calculating due dates and number
of
workdays for cycles. I do not want to include weekends, holidays, or
adjustment number of days. I have set up a separate worksheet and
entered
our
holidays and named the column "Holidays" but the formula is not
working
when
I add that section to it. I don't actually have the holidays in the
formula
because it is not working yet.

=IF(OR($L5="",$L5="NOT RECEIVED"),"",WORKDAY(Q5,U$1,Holidays)-Adj
days)
=If part not received yet, then do nothing, else calculate the due
date
for
this cycle by adding the number of workdays allowed to the due date of
the
last cycle, and do not include days listed in "Holidays" and subtract
number
days for adjustment.

Do these actually give me only workdays?
WORKDAY(Q5,U$1,Holidays)-Adj days
=IF(BN4="","",NETWORKDAYS(L4,BN4,Holidays)-BU4) (This one is suppose
to
count the total workdays use on entire project listed on row.)

This is the basic set up:
the number of
workdays allowed
for the cycle 10 22
Start Date Adjustments cycle 1 Due Date Repeats for cycles
Entered as # activities formula
until
project done.

11/20/05 2 12/1/05 formula

Formula Workdays-Holidays-adjustments
 

My "Holidays" was not working because I named the entire column Holidays. It
would not read the range. I ended up just pointing to the worksheet and
range. I'll mess with it a little more later. Thanks for your help!

I never have been on this discussion forum before. Looks like a look of
good info. I love learning new functions, etc.

"Bob Phillips" wrote:

Glenna,

I still cannot see why you are getting a problem.

To answer one specific question, if you subtract ADj Days as you show, it
will subtract calendar days, not workdays. To overcome this, you could
subtract ADj Days from the between cycle day amount, i.e. U$1-AdjDays

I have created a simple sample worksheet that shows all of that stuff
working fine, holidays, adjusted dates. Hopefully that will help. You can
get it at http://cjoint.com/?lqxVqdgY0n

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Glenna" wrote in message
...
Bob,
For formula in cell U5: =IF(OR($L5="",$L5="NOT
RECEIVED"),"",WORKDAY(Q5,U$1))
Column L is the start date of the project. Row 5 is one project.
Column Q contains the due date of previous cycle.
U$1 contain the number of workdays to add to due date of previous cycle
for
the due date of the next cycle.

I do not have holidays added in yet because it is not working. I have been
MANUALLY adjusting. There is getting to be too many manual adjustments.

My concern is also when I just add the minus adjustments after
WORKDAY(Q5,U$1) if those are actually substracting workdays or calendar
days.

"Bob Phillips" wrote:

Glenna,

On the face of it, that looks okay.

What data do you have in L5, Q5, and U1, and what do you get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Glenna" wrote in message
...
I am trying to get my Workdays and Networkdays formulas to work right.
I
am
measuring time in number of days, and calculating due dates and number
of
workdays for cycles. I do not want to include weekends, holidays, or
adjustment number of days. I have set up a separate worksheet and
entered
our
holidays and named the column "Holidays" but the formula is not
working
when
I add that section to it. I don't actually have the holidays in the
formula
because it is not working yet.

=IF(OR($L5="",$L5="NOT RECEIVED"),"",WORKDAY(Q5,U$1,Holidays)-Adj
days)
=If part not received yet, then do nothing, else calculate the due
date
for
this cycle by adding the number of workdays allowed to the due date of
the
last cycle, and do not include days listed in "Holidays" and subtract
number
days for adjustment.

Do these actually give me only workdays?
WORKDAY(Q5,U$1,Holidays)-Adj days
=IF(BN4="","",NETWORKDAYS(L4,BN4,Holidays)-BU4) (This one is suppose
to
count the total workdays use on entire project listed on row.)

This is the basic set up:
the number of
workdays allowed
for the cycle 10 22
Start Date Adjustments cycle 1 Due Date Repeats for cycles
Entered as # activities formula
until
project done.

11/20/05 2 12/1/05 formula