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 ROWS Function
  • Get nth match with INDEX / MATCH in Excel
  • Approximate match with multiple criteria in Excel
  • Count rows that contain specific values in Excel
  • How to use Excel MATCH Function

Data Analysis Examples

  • How to Create Gantt Chart in Excel
  • How to create Gauge Chart in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • How to sum a total in multiple Excel tables

Data Validation Examples

  • Excel Data validation must contain specific text
  • Excel Data validation date in specific year
  • Excel Data validation number multiple 100
  • Excel Data validation allow weekday only
  • How To Create Drop-down List in Excel

Calculate time difference in hours as decimal value in Excel

by

To get the duration between two times in as decimal hour (i.e. 3 hrs, 4.5 hrs, 8 hrs, etc.) you can use a formula based on the MOD function.

Formula

=MOD(end-start,1)*24

Explanation

In the example shown, the formula in D5 is:

=MOD(B2-A2,1)*24

Excel hours

In Excel, one day is the number 1, so 1 hour = 1/24 = 0.041666667. In other words, hours are just fractional parts of a day:

Worked Example:   Get work hours between dates and times in Excel
Time Fraction Hours
3:00 AM 0.125 3
6:00 AM 0.25 6
9:00 AM 0.375 9
12:00 PM 0.5 12
3:00 PM 0.625 15
6:00 PM 0.75 18
9:00 PM 0.875 21
12:00 AM 1 24

To convert these fractional values to decimal hours, just multiply by 24. For example .5 * 24 = 12 hours, .24 * 24 = 6 hours, etc.

Times that cross midnight

When times cross midnight, the problem becomes more tricky, since the end time will often be less than the start time.  One elegant way to handle this challenge is to add the MOD function to the formula. For example, to calculate hours between 9 PM and 3 AM:

=MOD(0.125-0.875,1)*24
=MOD(-0.75,1)*24
=0.25*24
=6

The MOD function takes care of the negative problem by “flipping” negative values to the required positive value. (In this way, the MOD function works a bit like a clock.

Worked Example:   Highlight every other row in Excel

Hours between times

To calculate hours between times, you can simply subtract the start time from the end time when both times are in the same day. For example, with start time of 9:00 AM and an end time of 3:00 PM, you can simply use this formula:

=(3:00 PM-9:00 AM)*24
=(.625-.375)*24
=.25 * 24
=6

Worked Example:   Count cells that contain odd numbers in Excel

Post navigation

Previous Post:

Calculate total hours that fall between two times in Excel

Next Post:

Two ways to sum time over 30 minutes 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 create email address from name in Excel
  • RIGHT, RIGHTB functions: Description, Usage, Syntax, Examples and Explanation
  • Extract word that begins with specific character in Excel
  • How to Check If A Cell Contains Specific Text in Excel
  • Join Text Strings Using Concatenate or ‘& Operator’ in Excel
  • Convert text to date in Excel
  • Convert time to time zone in Excel
  • Add decimal minutes to time in Excel
  • Convert Unix time stamp to Excel date
  • How to get workdays between dates in Excel
  • RRI function: Description, Usage, Syntax, Examples and Explanation
  • EFFECT function: Description, Usage, Syntax, Examples and Explanation
  • DURATION function: Description, Usage, Syntax, Examples and Explanation
  • Bond valuation example in Excel
  • FVSCHEDULE function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles