This is an awful solution but I think it works.
In the attached:
Sheet1:
You can delete everything below row 6 in this sheet.
Formula in C3, copy down one cell to C4
Formula in cell B4
Copy B4:C4 down as far as you need to.
These formulae all use a reference to cell A2 of
Sheet2, so on to
Sheet2:
This sheet contains a list of working days in column A but it needs to be updated from time to time for at least 2 reasons:
1. You change the start date in cell B3 of Sheet1
2. You change the holidays
Updating Sheet2:
Note that cell C2 formula is a direct reference to your start date at cell B3 of Sheet1 and the cells below C2 contain formulae to return all days sequentially for about 2 years.
To update column A of this sheet to contain only working days:
1. Ensure the table called
Holidays at cell H1 is up to date. It doesn't matter if there are older dates remaining in this table. You don't need to include Sundays. If a holiday happens to fall on a Sunday in this list it doesn't matter. Make sure all the dates are within the table limits (you can see the borders of this table).
2. There is a formula in column D (headed
Exclude) which returns TRUE if the corresponding date is to excluded as a working day. This is used in
Advanced Filter to create column A by…
2. Applying the
Advanced Filter as shown in the picture on
Sheet2. This produces a list of only working dates in column A (no holidays, no Sundays). This list is static; it doesn't change automatically when other cells change. This is why it needs to be updated from time to time.
Now you should get correct start and end production dates on
Sheet1.
Note that these dates are inclusive (Start date is at the beginning of that day, End date is at the end of the day of that date.