Add Time Together Limiting by Working Hours and Excluding Weekends and Holidays in Excel
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
=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.
Question? Ask it in our Excel Forum
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Tutorial: In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as...
Tutorial: Lets learn how to put multiple functions and formulas in a single cell in Excel in order t...
Tutorial: In this tutorial I am going to show you how to update, change and manage the data used by ...
Macro: This free Excel macro filters data in Excel using the autofilter feature in an Excel macro...
Macro: This free Excel macro filters data in Excel based on multiple criteria for one field in th...
Macro: This free Excel macro illustrates all of the possible parameters and arguments that yo...
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)