How to get an end date in a production cycle with given working days and holidays.

[V.BALJEKAR]

While planning a production lot, I need to calculate the end date which should not fall on a holiday.
Also after the 1st lot ends, how to skip the next working date, if there is a holiday after it (for the 2nd lot to commence).
I would appreciate to get a solution for it while working in Excel 2007 version.
Sample data file attached.

 

[BobBridges]

Holidays in Excel can be tricky. Internally they're stored as the number of days since Jan 1, 1900, so the 4th of July can fall on day 45477, 45842, 46207 and so on. You can translate the date to just month and day, and thus spot "07-04" without regard to the year, but a) you'd have to do that individually for all the other holidays you care about too, and b) some holidays don't fall on a particular date—Thanksgiving in the US, for example, is just the 4th Thursday of November. Excel can figure that out, too, but the point is that you may need a separate logic for each holiday that you choose to avoid in your program.

And that, by the way, is another point: You're going to need to write a program to calculate all this, so this question really belongs in the "VBA Macros" forum.

 

[p45cal]

It looks like you're using Excel 2007 ! Is that correct?
If you were using a more recent version it would be easy with WORKDAY.INTL which allows you to specify exactly what weekend days are. Am I right that your weekend is only Sunday?
Excel 2007 only allows WORKDAY which assumes your weekends are Sat and Sun, so it's probably no use here.
Both WORKDAY and WORKDAY.INTL can use a list of holidays which we can get from your list of dates.
So can you tell us what version of Excel you're using? It would be better if you were able to say you're using at least Excel 2010 then WORKDAY.INTL would be available. Then we'd need only a list of holidays (the Sunday-only weekends would be handled by the function) which from your workbook would only be a short list:
7/Sep/24
12/Oct/24
1/Nov/24
25/Dec/24
26/Jan/25


If you were able to say you were using Excel 2021 then it would be even easier.
Awaiting your response…

 

[V.BALJEKAR]

Holidays in Excel can be tricky. Internally they're stored as the number of days since Jan 1, 1900, so the 4th of July can fall on day 45477, 45842, 46207 and so on. You can translate the date to just month and day, and thus spot "07-04" without regard to the year, but a) you'd have to do that individually for all the other holidays you care about too, and b) some holidays don't fall on a particular date—Thanksgiving in the US, for example, is just the 4th Thursday of November. Excel can figure that out, too, but the point is that you may need a separate logic for each holiday that you choose to avoid in your program.

And that, by the way, is another point: You're going to need to write a program to calculate all this, so this question really belongs in the "VBA Macros" forum.

Yes, it sure is tricky with Excel 2007. Any ways, thanks for your response.

 

[V.BALJEKAR]

It looks like you're using Excel 2007 ! Is that correct?
If you were using a more recent version it would be easy with WORKDAY.INTL which allows you to specify exactly what weekend days are. Am I right that your weekend is only Sunday?
Excel 2007 only allows WORKDAY which assumes your weekends are Sat and Sun, so it's probably no use here.
Both WORKDAY and WORKDAY.INTL can use a list of holidays which we can get from your list of dates.
So can you tell us what version of Excel you're using? It would be better if you were able to say you're using at least Excel 2010 then WORKDAY.INTL would be available. Then we'd need only a list of holidays (the Sunday-only weekends would be handled by the function) which from your workbook would only be a short list:
7/Sep/24
12/Oct/24
1/Nov/24
25/Dec/24
26/Jan/25


If you were able to say you were using Excel 2021 then it would be even easier.
Awaiting your response…

I am using Excel 2007 hence I feel handicapped. I was trying out simpler formulas but ran into circular reference.
I am now looking for a formula which facilitates to skip to next working day if any intermediate date or end date falls on the holiday.
If this thought can give some idea to work out a formula, it would be great.

 

[p45cal]

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.

 

[V.BALJEKAR]

Yes, its working as I require and thank you for the clear explanation.
p45cal, Thanks a lot once again.