Add two times together to get a future date and time that falls within working hours, excluding weekends and any holidays. I'll show you how to do this using a formula.
=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$E$2,1,0),$G$2:$G$14)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$E$2,$D$2+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$E$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0))
There are 4 required fields that need to be filled-in to use this formula and an optional one.
Start Date/Time: This is the starting date and time for the calculation. The date and time should be in the same cell.
# of Hours: This is the amount of time to add to the start date/time. This should NOT be formatted as a date. It must be a regular number. To add minutes, you put them in 'fraction-of-an-hour' format; so, 20 hours and 30 minutes becomes 20.5. Check out this tutorial if you need to change time stored as a decimal to a percentage of an hour.
Workday Start: The time when the workday begins (must be formatted as time).
Workday End: The time when the workday ends (must be formatted as time).
Holidays: Optional. A list of any holidays. All holidays should be input as a date using a date format. This can be left empty if it's not needed.
This example shows you how to use every part of the formula.
The result is displayed in cell A5.
The default cell references for this formula are as follows:
A2: Start Date/Time
B2: Number of Hours to add to the Date/Time
D2: When the workday starts.
E2: When the workday ends.
G2:G140: List of any holidays.
You can, of course, change the cell references as needed to fit your data, just be careful when you do that because this is a large formula and the same cell references are repeated many times.
You can also remove the Holiday cell reference, if you don't want to include any, by removing ,$G$2:$G$140 from the formula.
My goal in this tutorial was to give you this formula and explain how to use it and not how to create it, because, let's be honest, you probably will never need to know how to make this from scratch. However, if you do need to understand how to create this formula, first, learn all of the time functions and how they work together and then start to pick-apart the formula.
Download the file attached to this tutorial to work with this example in Excel.