Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Convert text string to valid reference in Excel using Indirect function
  • How to get relative row numbers in a range in Excel
  • How to use Excel LOOKUP Function
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to use Excel COLUMN Function

Data Analysis Examples

  • Conditional Formatting Data bars Examples in Excel
  • Filter Data Based on Date in Excel
  • How to create a Histogram in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to calculate current stock or inventory in Excel

Data Validation Examples

  • Excel Data validation allow weekday only
  • Excel Data validation whole percentage only
  • Excel Data validation must not contain
  • How To Create Drop-down List in Excel
  • Excel Data validation unique values only

Get work hours between dates and times in Excel

by

To calculate total work hours between two dates and times, you can use a formula based on the NETWORKDAYS function.

Formula

=(NETWORKDAYS(start,end)-1)*(upper-lower)
+IF(NETWORKDAYS(end,end),MEDIAN
(MOD(end,1),upper,lower),upper)
-MEDIAN(NETWORKDAYS(start,start)
*MOD(start,1),upper,lower)

Explanation

In the example shown, E5 contains this formula:

=(NETWORKDAYS(B5,C5)-1)*(upper-lower)
+IF(NETWORKDAYS(C5,C5),MEDIAN
(MOD(C5,1),upper,lower),upper)
-MEDIAN(NETWORKDAYS(B5,B5)
*MOD(B5,1),upper,lower)

where “lower” is the named range H5 and “upper” is the named range H6.

How this formula works

This formula calculates total working hours between two dates and times, that occur between a “lower” and “upper” time. In the example shown, the lower time is 9:00 AM and the upper time is 5:00 PM. These appear in the formula as the named ranges “lower” and “upper”.

Worked Example:   Add decimal hours to time in Excel

The logic of the formula is to calculate all possible working hours between the start and end dates, inclusive, then back out any hours on the start date that occur between the start time and lower time, and any hours on the end date that occur between the end time and the upper time.

Worked Example:   nth smallest value with criteria in Excel

The NETWORKDAYS function handles the exclusion of weekends and holidays (when provided as a range of dates). You can switch to NETWORKDAYS.INTL if your schedule has non-standard working days.

Worked Example:   If cell is x or y and z in Excel

Alternatively

If start and end times will always occur between lower and upper times, you can use a simpler version of this formula:

=(NETWORKDAYS(B5,C5)-1)*(upper-lower)
+MOD(C5,1)-MOD(B5,1)

No start time and end time

To calculate total work hours between two dates, assuming all days are full workdays, you can use an even simpler formula:

=NETWORKDAYS(start,end,holidays)*hours

Post navigation

Previous Post:

Get work hours between dates custom schedule in Excel

Next Post:

Get work hours between dates in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to extract multiple lines from a cell in Excel
  • How to find nth occurrence of character in Excel
  • How to translate letters to numbers in Excel
  • How to get page from URL in Excel
  • UNICHAR function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate future date say 6 months ahead in Excel
  • Get date from day number in Excel
  • Convert date to month and year in Excel
  • Display Date is same month in Excel
  • Get work hours between dates in Excel
  • MDURATION function: Description, Usage, Syntax, Examples and Explanation
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • NPER function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest for given period in Excel
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles