How to account for weekends in date calculations?

Posted by Matthew Beckett on August 29, 2001 7:47 AM

I have a spreadsheet that tracks the date an item arrives and the date that our response is sent - the deadline for this is 20 working days. The current formula does the simple substraction but I can't get it to account for the weekends.

Is there a formula that would calculate this? There is a today() cell that I have tried to get it calculate from but without success - please help.

Many thanks

Matthew

Posted by Aladin Akyurek on August 29, 2001 7:53 AM

You need to use the NETWORKDAYS function for that, which is available if you activate Analysis Toolpak via Tools|Add-ins:

=NETWORKDAYS(arrival-date,response-date,holidays)

The 3rd arg is optional: if you are interested excluding holidays, you have put dates for holidays in some range and enter that range in the above formula.

Aladin

============

Posted by Mark W. on August 29, 2001 7:53 AM

Take a look at the NETWORKDAYS worksheet function.
You may need to add the Analysis ToolPak.

Posted by Matthew Beckett on August 29, 2001 8:31 AM

Many thanks - never knew it existed. Can the arguments (i.e. arrival-date) be a cell reference? I have tried the function and checked help but I get a #value error.

Thanks in advance

Matthew

Posted by Aladin Akyurek on August 29, 2001 8:51 AM

Yes.

I have tried the function and checked help but I get a #value error.

If you use date constants such as 1-Jan_01 in the formula, you need tp place them between double quotes like this:

=NETWORKDAYS("1-3-2001","10-4-2001")

Aladin

Posted by Matthew on August 29, 2001 9:24 AM

Thanks - I've got it working for the fixed (i.e. "29-08-01") arguments but I can't get it to work with cell references. What am I doing wrong?

e.g. =NETWORKDAYS(DATEVALUE("B4"),DATEVALUE("A4"))

where B4 (response date) minus A4 (date receieved) - I've also reversed the references with no solution.

Thanks.

Matthew - never knew it existed. Can the arguments (i.e. arrival-date) be a cell reference?

Posted by Aladin Akyurek on August 29, 2001 9:40 AM

> I've got it working for the fixed (i.e. "29-08-01") arguments but I can't get it to work with cell references. What am I doing wrong?

> e.g. =NETWORKDAYS(DATEVALUE("B4"),DATEVALUE("A4"))

> where B4 (response date) minus A4 (date receieved) - I've also reversed the references with no solution.

=NETWORKDAYS(A4,B4) should work. If not, try:

=NETWORKDAYS(A4+0,B4+0) in case your dates are "text entered".

Aladin