Back to Dates in Excel archive index

Back to archive home

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

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

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

Take a look at the NETWORKDAYS worksheet function.

You may need to add the Analysis ToolPak.

You may need to add the Analysis ToolPak.

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

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

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?

> 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.

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.