Calculate 'REAL' end date based on public holidays/scheduled 'breaks'

Hi there all!

I've been working on a little tool that will allow me to automate a 'real' end date, based on public holidays or certain date ranges being in between a scheduled start date and a scheduled end date (that thus bump the 'real' end date forward) this is related to an 'assessment period' - i.e. an employee is given 5 to 10 to 20 etc days to complete a particular assessment. Depending on when they began at the company, will also dictate the expected finish dates as it relates to the 'start date'.

I have written a macro tool that allows this to happen 'more or less' but currently only if a 'scheduled' end date falls on a specific date in the range. Example:

Start Date of Employee = 1st December 2015

From this point, they are expected to complete 'x' amount of assessments on our system across a time period - some are longer than others, and as such they are given x or y amount of time depending on length of the assessment.

So, let's say Start Date = 1/12/2015, the first assessment they are given is estimated to take 5 days to complete, and this is what they are given. So, the 'Expected Finish Date is 06/12/2015 (dd-mm-yyyy). In my eyes this is good, as I know there are no public holidays or specified vacation blocks during this time, so the expected finish date is correct.

Each employee gets 5 days break in between commencing a new assessment - so the next assessment date down would be 06/12/2015 + 5 and thus 11/12/2015.

So new start date = 11/12/2015, and they have 15 days to complete the next, making an 'expected finish date' of 26/12/2015. The x-mas period or 'break' at this company is 14/12/2015 through till 04/01/2016. Essentially I am trying to now get the 'real' finish date, based on the fact they will not be at work to complete the assessment by the 'expected date'. However, they will be at work for 3 days beforehand, and this will count to their total - so it should be 3 days before the holiday, then we 'put it on hold' until the end of the holiday, at which point we add 12 days to make up 15 total.

So the real date should be 16/01/2016. This 'real date' will now serve as the basis for the next 'assessment start date', so it will be real date + 5 = new assessment start date.

The problem I have, is that I can't work out a concise method with which to 'capture' that a holiday may occur within the two date ranges, i.e. between start/end and then specify how many days should be added on at the END of that holiday period.

I'm pretty sure in my VB code I'm going to have to calculate it line by line, so that as each new 'real finish date' is calculated (and pushes out the days) it will capture it and recalc it to suit.

Currently I have it able to VLOOKUP the expected finish date, and return that it's 'Christmas Break 2015', and how many days till the end of that holiday and then to add that many to the holiday end date, defined in my code as 'xmasend', 'easterend' etc.

My brain is frazzled from trying to think of simpler ways, rather than keep mucking around experimenting with different formulas (and not having much success), so I thought I'd open it up here to see if anyone has any ideas.

I've attached the spreadsheet that gives a fairly clean example of what I need.

Thanks in advance folks, any help will be much appreciated. Any extra questions, please ask!

Cheers!

Take a look at your example again.

01/12/15 is a Tuesday. Plus 5 days takes you to 5/12/2015 (if the 1/12/15 is Day 1). That is a Saturday. So the answer should be 8/12/2015???
01/12/15 is a Tuesday. Plus 5 days takes you to 6/12/2015 (if the 1/12/15 is Day 0). That is a Sunday. So the answer should be 8/12/2015???

So is the start day, Day 1 or Day 0?

Does this formula give you the expected results?
=WORKDAY(A2,B2,$F$2:$F$27)