Formula Workdays-Holidays-adjustments

[Glenna]

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

 

[Bob Phillips]

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]

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]

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]

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.