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

Lookup and Reference Examples

  • How to use Excel OFFSET function
  • How to get last row in mixed data with blanks in Excel
  • Last row number in range
  • Perform case-sensitive Lookup in Excel
  • How to get first column number in range in Excel

Data Analysis Examples

  • Number and Text Filters Examples in Excel
  • How to create Checklist in Excel
  • Conditional Formatting Rules in Excel
  • Excel Bar Chart
  • How to conditionally sum numeric data in an Excel table using SUMIFS

Data Validation Examples

  • How To Create Drop-down List in Excel
  • Excel Data validation must not contain
  • Excel Data validation date in specific year
  • Excel Data validation must begin with
  • Prevent invalid data entering in specific cells

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:   Count cells that contain odd numbers 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:   Count Errors 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:   List holidays between two dates 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 count total characters in a range in Excel
  • Remove text by position in a cell in Excel
  • Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel
  • Find most frequent text within a range with criteria in Excel
  • How to count keywords in a range of cell
  • Excel Date & Time Functions Example
  • Get fiscal quarter from date in Excel
  • How to calculate Day of the Year in Excel
  • How to calculate most recent day of week in Excel
  • Convert date to Julian format in Excel
  • XIRR function: Description, Usage, Syntax, Examples and Explanation
  • RECEIVED function: Description, Usage, Syntax, Examples and Explanation
  • DB function: Description, Usage, Syntax, Examples and Explanation
  • TBILLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • AMORDEGRC function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles