Adding hours to start time - Business hours/holidays/weekends

I'm trying to determine the end time of an SLA by using the planned start date plus the SLA goal accounting for business hours, holidays, and weekends.

I found this formula (by daddylonglegs) which appears to work for the original poster, but does not work correctly for me. I've adapted it to my spreadsheet.

Please Login or Register  to view this content.

HLDY = Named range of Holidays
BusinessStart = named range for the begining of the Business Day (6:00)
BusinessEnd = named range for end of business day (18:00)
M2 = Start time
X2 = Hours to add

In M2 I have the date of 3/17/2014 10:00 (this date falls on a snow day I added as a HLDY)
X2 = 15:00 (hours)

the end result is 3/17/2014 10:37

The result should be 3/19/2014 9:00. Because the start time is on a HLDY it should rollover to the beginning of the next business day of 3/18/2014 6:00 and then add 15:00 hours.

It also needs to account for goal times of greater than 24 hours (i.e. 36:00, 60:00)

Any help would be much appreciated.

Thank you for your time.

Also. if it matters. I tried the above formula with a date that actually started WITHIN business hours and not on a Holiday and it didn't give me the correct date time.

3/18/2014 10:00 + 15:00 came out to 3/18/2014 10:37

Yes sir, I would like it to work outside of working hours, just roll over to the next Business Day and/or Time.

So, I believe I was able to modify your formula and get it working how I want to. It appears to work for now, I've ran it through several tests.

HTML Code: 

In the portion of WORKDAY, you had the days as -1. I changed it to 0 or 1 (depending on where the start date began - ie weekend or a holiday)

If you wouldn't mind looking at it and tell me if I've left anything out or forgotten anything...or any improvements.

Thank you sir for all of your help.

Marking this as Solved The last formula appears to be working the way I want it to.

Thanks for all of your help,

Tim