Working days between dates but without networkdays

[Gixerstu]

Hi All,

I need to calculate the working days (less bank holidays) between two dates but this will be on a downloadable sheet and I can't trust the users to download the analysis toolpak.

The problem is that I have a supplier lead time that I need to enforce, so I need a date that is X working days from a date entered on the sheet.

Does anyone have any ideas how to acheive this?

 

[Andrew Poulsom]

There is a replacement formula here:

http://www.dicks-blog.com/archives/2004/12/19/replacing-the-analysis-toolpak-addin-part-2/

 

[barry houdini]

With start date in A2 and end date in B2 you can use this formula also

=SUM(INT((WEEKDAY(A2-{2,3,4,5,6})+B2-A2)/7))-SUMPRODUCT((holidays>=A2)*(holidays<=B2)*(WEEKDAY(holidays,2)<6))

where holidays is a named range including your holiday dates

 

[Gixerstu]

Thanks for the replies.

The issue I have is that I am trying to work out the end date.

So what date is 50 working days away from today for example?

 

[barry houdini]

OK, normally that would be WORKDAY in the form

=WORKDAY(A2,B2)

where A2 is the start date and B2 days to add

.....but that's also part of Analysis ToolPak

There's a replacement formula on the site that Andrew linked to which accommodates negative days (to get a start date in the past given an end date) or you can use this version if you always want a future date

=SMALL(IF(WEEKDAY(A2+ROW(INDIRECT("1:"&B2*10)),2)<6,IF(COUNTIF(holidays,A2+ROW(INDIRECT("1:"&B2*10)))=0,ROW(INDIRECT("1:"&B2*10)))),B2)+A2

needs to be confirmed with CTRL+SHIFT+ENTER

 

[Gixerstu]

Barry,

thank you very much for the help. It works superbly.

Stu