Networkdays function problem

[Lee]

I am using the Networkdays function in spreadsheet to
calculate the business days between two dates, Received
Date and Process Date. I have a list of holidays that I
am referencing as well.

The problem is that I've noticed that it is giving me
incorrect results. For example, if an item was received
on 4/29/03 (Tuesday) and processed on 4/30/03 (Wednesday),
we would count the turnaround time as being one business
day. The Networkdays result is 2 days. If the item was
received on a Friday and the item was processed on
Saturday, it shows the result as one business day.

Is there a way I can correct this? It's messing up my
calculations badly. Is there a way I can use the Weekday
function with this to tell it something like, "if the
received date is anything other than Friday, subtract one
day"??

 

[Tim Otero]

Hi Lee,

This is actually not an incorrect result. NETWORKDAYS is supposed to include
both the beginning and ending date. If you are working on a project on the
dates 7/1/2003 through 7/3/2003, you would want the formula to return three
days, which is what it does. Any different and you would be shorted by one
day.

For your purposes you'd want to just subtract one day from the total. For
example,

=NETWORKDAYS(A1, A2)-1

tim

 

[Aladin Akyurek]

=NETWORKDAYS(ReceivedDate,ProcessedDate,Holidays)-(WEEKDAY(ReceivedDate,2)=5
)

 

[Tim Otero]

Hi Lee,

Try Aladin's solution, I've tested it and believe it works for what you
need.

tim

 

[Myrna Larson]

If the received date is Friday and the 2nd date is Monday rather than Saturday, do you want to
subtract one then?

If your intervals never span the weekend, maybe you can use

=MAX(1,NETWORKDAYS(B12,C12)-1)

which always subtracts 1 but won't return a result of 0 or less.

 

[Myrna Larson]

Could the essence of the problem be that Saturday is a workday for you, and only Sundays and
holidays are not workdays?

 

[Lee]

Thanks! The solution worked perfectly.

-----Original Message-----
Hi Lee,

Try Aladin's solution, I've tested it and believe it works for what you
need.

tim




.