Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Subtotal function in Excel
  • How to add Trendline to a chart in Excel
  • Managing Conditional Formatting Rules in Excel
  • Understanding Pivot Tables in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel

References

  • How to get relative row numbers in a range in Excel
  • How to get last row in text data in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Create hyperlink with VLOOKUP in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation must begin with
  • Excel Data validation don’t exceed total
  • Excel Data validation with conditional list
  • Excel Data validation allow uppercase 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:   Sum every nth column 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:   Highlight every other row 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:   How to count line breaks in cell in Excel worksheet

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 day from date in Excel

Next Post:

Calculate loan interest in given year 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

Logical Functions

  • Invoice status with nested if in Excel
  • How to use Excel TRUE Function
  • IF with wildcards in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples

Date Time

  • How to get same date next year or previous year in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Add months to date in Excel
  • How to calculate next anniversary date or birthday in Excel
  • Convert decimal seconds to Excel time

Grouping

  • If cell contains one of many things in Excel
  • Categorize text with keywords in Excel
  • Map inputs to arbitrary values in Excel
  • How to randomly assign people to groups in Excel
  • Group times into unequal buckets in Excel

General

  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • 44 Practical Excel IF function Examples
  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
  • Basic numeric sort formula in Excel
  • How to generate random number weighted probability in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning