Removing Holiday's from date calculation

[Timjor]

OK, I've been trying to remove holidays from a date calculation (already removed weekends, that i posted earlier), but I can;t seem to remove a range of dates (holidays) from the calculation.

As of right now, I am stumped. Please see the attached file, and let me know if I can provide more input to the problem. Any help would be appreciated!

Thanks

Tim

[Pete_UK]

Take a look at the NETWORKDAYS function, where you can point to a holiday list and the function can take account of this. If you have XL2003 or earlier, you will need to install the Analysis ToolPak.

Hope this helps.

Pete

[Timjor]

NETWORKDAYS returns a number based on a range minus the holidays. If you take a look at the spreadsheet I attached to my original post, I need to return a date based on a date minus a qty of days. Unless I am missing something, the NETWORKDAYS won't return what I need.

Thanks

Tim

[Timjor]

Any help would be appreciated. Looking to subtract days from a date and exclude only a defined set of holidays. WORKDAYS works for the working week, but not if I want to include weekends and exclude holidays from the calculations.

[mahju]

Hi
As your data is not consistant I want to be clear
As in the first solution you have changed 7/18/2012 to 7/16/2012 nut 7/18/2012 is not weekend nor it is included in the holidays.
Please explain if after substrection day falls on week day or holiday go to next working day or go back
Regards

[Timjor]

Hi Mahju - thanks for responding.

OK, I have tried to clarify on the attached worksheet. I can do the calculation to exclude weekends and holidays using the WORKDAY function for processes that can only run on weekdays. I now need a calculation that will exclude holidays as 'working days' on the non WORKDAY functions. I hope the attached helps clarify.

Thanks

Tim

[Timjor]

Can anyone help?

Tim

[Timjor]

OK, solved it (eventually) thanks to a different forum. I wanted to post the solution here, so everyone can see it (if needed).

Solution was:

=WORKDAY.INTL(A1,-B1,"0000000",C1:C10)

Where A1 is the end date
B1 is the number of days for the process
"0000000" is the string signifying all the days are working days
C1:C10 is the holiday date range

[daddylonglegs]

Note that it helps if you specify which version you are using - that formula will only work in Excel 2010 and most contributors here probably won't suggest that unless you specifically indicate you are using Excel 2010

This "array formula" should do the job in earlier versions

=A1-SMALL(IF(COUNTIF(C$1:C$10,A1-ROW(INDIRECT("1:"&COUNT(C$1:C$10)*B1+B1)))=0,ROW(INDIRECT("1:"&COUNT(C$1:C$10)*B1+B1))),B1)

confirmed with CTRL+SHIFT+ENTER

[k_vivek27]

It would be helpful if someone let me know how to use WORKDAY.INTL function in excel 2007.

Thanks,

Vivek

Note that it helps if you specify which version you are using - that formula will only work in Excel 2010 and most contributors here probably won't suggest that unless you specifically indicate you are using Excel 2010

This "array formula" should do the job in earlier versions

=A1-SMALL(IF(COUNTIF(C$1:C$10,A1-ROW(INDIRECT("1:"&COUNT(C$1:C$10)*B1+B1)))=0,ROW(INDIRECT("1:"&COUNT(C$1:C$10)*B1+B1))),B1)

confirmed with CTRL+SHIFT+ENTER