Adding days to a specific date

Question

I have a certain date and I want basically to do the following: add 60 days to it (without skipping weekends or holidays - so basically would be date+60), however if the resultant date (the 60th day after the start date) happens to be NOT a working day (a weekend or a holiday) take me to the next working day (skipping weekend and holidays). When adding the 60 days to the date, it doesn't skip any weekends or holidays, but if the result date is not a working day, take me to the next.

for example: 01/25/2016 + 60 = 03/25/2016, which is a holiday, so take me to the next working day (as it is Friday, it will return me the next Monday, as Monday is not a holiday in this case, if it were, i would need it to check if Tuesday is a holiday and if it isn't, return me Tuesday instead).

How can I do this? I would preferably do it with formulas instead of VBA but programming this would definitely not be a problem.

PatricK PatricK · Accepted Answer · 2016-03-30T22:39:22

You can do Next Workday (Mon - Fri) after 60 days easily with Formula.

Say your Date is on A1, and the next workday after 60 days is stored in B1, then B1 can use this formula:

=A1+60+IF(WEEKDAY(A1+60,2)>5,8-WEEKDAY(A1+60,2),0)

Unless you have a table list of the Public Holidays, you cannot do much when the "Next Workday" is a Public Holiday.

If you do, add IFERROR() and VLOOKUP(), but there is still a chance of both Mon & Tue are public holiday (X'mas can do this). You will need a UDF to loop through the calculated next workday until it is not public holiday.

Adding days to a specific date

1 Answers