Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home



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


Re: How to account for weekends in date calculations?

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

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


Re: How to account for weekends in date calculations?

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.


Re: How to account for weekends in date calculations?

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


Re: How to account for weekends in date calculations?

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


Re: How to account for weekends in date calculations?

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?


Re: How to account for weekends in date calculations?

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





This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.