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 MMULT Function
  • How to get relative column numbers in a range in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Count unique text values with criteria
  • INDEX function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to Create Thermometer Chart in Excel
  • How to Use Solver Tool in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Get column name from index in Excel Table

Data Validation Examples

  • Excel Data validation must not contain
  • Excel Data validation allow weekday only
  • Excel Data validation specific characters only
  • Excel Data validation unique values only
  • Excel Data validation date in specific year

Convert decimal hours to Excel time

by

This tutorial shows how to Convert decimal hours to Excel time using example below.

To convert hours in decimal format to a value Excel recognizes as time, divide by 24.

Formula

=hours/24

Explanation

In the example shown the formula in C5 is:

=B5/24

which returns 0.04167, the equivalent of 1 hours. Cell D6 shows the same result formatted as time, which displays 1:00.

How this formula works

In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below:

Hours Fraction Value Time
1 1/24 0.04167 1:00
3 3/24 0.125 3:00
6 6/24 0.25 6:00
4 4/24 0.167 4:00
8 8/24 0.333 8:00
12 12/24 0.5 12:00
18 18/24 0.75 18:00
21 21/24 0.875 21:00

This means if you have a decimal number for hours, you can simply divide by 24 to get the correct representation of hours in Excel. After dividing by 24, you can apply a time format of your choice, or use the result in a math operation with other dates or times.

In the example, since B10 contains 12 (representing 12 hours) the result is 12/24 = 0.5, since there are 12 hours in a half of day. Once a time format like h:mm has been applied, Excel will display 12:00.

Durations longer than 24 hours

To display hours that represent a duration longer than 24 hours, you’ll need to adjust the number format. Just wrap the h in square brackets like so:

[h]:mm

To display in minutes, you can do the same thing with m:

[m]

Post navigation

Previous Post:

Convert decimal minutes to Excel time

Next Post:

Convert date to text 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 text between parentheses in Excel
  • UPPER function: Description, Usage, Syntax, Examples and Explanation
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • Split dimensions into two parts in Excel Worksheet
  • Remove leading and trailing spaces from text in one or more cells in Excel
  • Add days exclude certain days of week in Excel
  • Add years to date in Excel
  • Calculate expiration date in Excel
  • Add workdays to date custom weekends in Excel
  • Display Date is same month in Excel
  • RRI function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYSNC function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate present value of annuity in excel
  • SLN function: Description, Usage, Syntax, Examples and Explanation
  • PMT function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles