Excel Formulas to Get Work Hours between Dates with Custom Schedule

Let’s say you have 2 dates in cells A1 and B1 indicating starting and ending timestamps of an activity. And you want to calculate how many workings hours the task took based on a certain schedule. How can you calculate them quickly? This guide will show the simple formula to get the work hours between two dates with a custom schedule in Excel. Let’s get them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

General Formula:

To calculate the total working hours between two dates with a custom schedule, you can use the below formula.

=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&”:”&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&”:”&end)),holidays,0)))

Syntax Explanations:

SUMPRODUCT – ** The **SUMPRODUCT function multiplies the corresponding arrays or ranges and returns the sum of the products.
MID – In Excel, the MID function helps to extract the number (starting from the left side) or characters from the given string.
WEEKDAY – This function returns a number (ranging from 1 to 7) that represents the day of the week for a given date. Read more on the WEEKDAY function.
ROW – The ROW function returns the row number for a reference.
INDIRECT – In Excel, this function helps to return a valid reference from a given text string. Read more on the INDIRECT function.
ISNA – The ISNA function will return TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type.
MATCH – This function helps to locate the position of a lookup value in a row, column, or table. Read more on the MATCH function.
Start – It specifies the starting date from your worksheet.
*End * – It represents the end date from your worksheet.
Schedule – It specifies the number of working hours per day.
Multiplication (*) – In this symbol will multiply any two values or numbers.
Comma symbol (,) – It is a separator that helps to separate a list of values.
Parenthesis () – The main purpose of this symbol is to group the elements.

Practical Example:

Let’s consider the below example image.

Here, we will enter the input values such as starting date , ending date in Column B and Column C.
Now, we need to find out the total working hours between two dates based on the criteria which are given in Column D.

Select any cell and type the above-given formula.

Finally, press ENTER to get the result out, if you need, drag the fill handle over range to apply the formula.

Conclusion:

So yeah guys this is how we can calculate the work hours between two dates with a custom schedule in Excel. I hope you like it. Leave a comment or reply below to let me know what you think!

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **