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
Last edited by Timjor; 02-15-2012 at 01:26 PM.
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
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
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.
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
Mahju
Mark the thread as solved if you are satisfied with the answer
Rule 9
Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.
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
Last edited by Timjor; 02-10-2012 at 06:56 PM.
Can anyone help?
Tim
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
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
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks