Exclude weekends in date counting

Hi guys,

I have been trying to count dates in my spreadsheet. The dates are supposed to go back in time (what they do). But I want them to jump over the weekends' dates instead of landing on them.

What I have is Start Date, Req'd Qty, Days it takes to make, Date it is required to start production (which I have a problem with).

A
1 26-Nov [Thursday]
2 1500
3 5
4 'Problem' I used for this cell that formula: =if(A2>0,A1-A3,"") , as I don't want it to show anything if there is no requirements.

It comes back with 21-Nov which is Saturday when I want it to come back with 19-Nov (Thursday) as we only have 5 working days in a week.

If anybody has an idea how to go around those weekend days please help, as it would help me a lot.

Thanks for reading and suggesting.

I also attached a little test file.

You can make use of WORKDAY (see Note at foot of post)

=IF(A2>0,WORKDAY(A1,-A3,holidays),"")

where holidays is a named range containing a list of public holidays to be excluded also - this is optional so remove that in red if not required.

adjust also the day offset by 1 (-(A3)+1) if you wish start date to be inclusive in the calculation (and assuming of course A1 is a workday!)

NOTE: pre XL07 you need to activate the Analysis ToolPak to make use of WORKDAY (else you will received #NAME? error)

Hi DonkeyOte,

Thanks for your help. I have tried your suggestion.:

Originally Posted by DonkeyOte

=IF(A2>0,WORKDAY(A1,-A3,holidays),"")

Didn't work - Returning #NAME?. I didin't include any holidays if it could cause a problem

Originally Posted by DonkeyOte

NOTE: pre XL07 you need to activate the Analysis ToolPak to make use of WORKDAY (else you will received #NAME? error)

Is it because pre XL07 means Excel version lower than 2007? If so how can I activate that toolpak, as I cannot find it in options.

If you can respond to this it would be brilliant.

Thanks anyway.

To activate the Add-in go to Tools -> Add-Ins and "check" Analysis ToolPak

Re: holidays - yes if you don't have a range then remove the text in red from your formula, ie:

=IF(A2>0,WORKDAY(A1,-A3),"")