This post will guide you how to add days to a given date excluding weekends and holidays with a formula in Excel.
Add Days to Date Excluding Weekends and Holidays
Assuming that you have a list of data in range B1:B4, which contain date values. And you want to add days to a given date excluding weekdays and all the specified holidays in Excel. How to do it. You can use a formula based on the WORKDAY function to achieve it. Like this:
Cell D2 contain the number of days to be added to the date. C2:C3 is the list of holiday dates.
Type this formula into a blank cell and press Enter key. And go to HOME tab, select Short Date from the Number Format drop down list under Number group.
And then drag the AutoFill handle over to other cells to apply this formula.
If you only want to add days to date excluding weekdays, you can use the following formula based on WORKDAY function. Like this:
- Excel WORKDAY Function
The Excel WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified. The syntax of the WORKDAY function is as below:=WORKDAY(start_date, days, [holidays])…