Add Time Together Limiting by Working Hours and Excluding Weekends and Holidays in Excel

Add to Favorites

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.

Sections:

The Formula

How to use the Formula

Notes

The 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))

How to use the Formula

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.

Final Result:

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.

Notes

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
How to Add Boxes, Buttons, Arrows, Speech-Bubbles, Hearts, and More to a Spreadsheet in Excel
Tutorial: In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as...
How to Use Multiple Functions and Formulas in a Single Cell in Excel
Tutorial: Lets learn how to put multiple functions and formulas in a single cell in Excel in order t...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a...
Update, Change, and Manage the Data Used in a Chart in Excel
Tutorial: In this tutorial I am going to show you how to update, change and manage the data used by ...
Filter Data in Excel - AutoFilter
Macro: This free Excel macro filters data in Excel using the autofilter feature in an Excel macro...
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
Macro: This free Excel macro filters data in Excel based on multiple criteria for one field in th...