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 ROW Function
  • Count rows that contain specific values in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in text data in Excel
  • How to get first column number in range in Excel

Data Analysis Examples

  • How to create dynamic reference table name in Excel
  • How to Sort by Color in Excel
  • Understanding Pivot Tables in Excel
  • How to create Gauge Chart in Excel
  • How To Create Pareto Chart in Excel

Data Validation Examples

  • Excel Data validation require unique number
  • Excel Data validation unique values only
  • Excel Data validation with conditional list
  • Excel Data validation no punctuation
  • Data validation must not exist in list

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:   Tax rate calculation with fixed base 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:   List holidays between two dates 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:   Highlight multiples of specific value 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
  • Remove text by position in a cell in Excel
  • NUMBERVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Remove text by matching in a cell in Excel
  • How to count total words in a range in Excel
  • How to get last word in a cell in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • Count day of week between dates in Excel
  • How to get year from date in Excel
  • Add workdays no weekends in Excel
  • How to calculate months between dates in Excel
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
  • AMORDEGRC function: Description, Usage, Syntax, Examples and Explanation
  • ODDFPRICE function: Description, Usage, Syntax, Examples and Explanation
  • PRICEMAT function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles