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

Data Analysis

  • Use Data Form to input, edit and delete records in Excel
  • How to create running total in an Excel Table
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Move chart to a separate worksheet in Excel
  • How To Sort One Column or Multiple Columns in Excel

References

  • Extract data with helper column in Excel
  • Count rows that contain specific values in Excel
  • How to get last row in numeric data in Excel
  • Count unique text values with criteria
  • Two-way lookup with VLOOKUP in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation only dates between
  • Prevent invalid data entering in specific cells
  • Excel Data validation specific characters only
  • Excel Data validation no punctuation

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:

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

  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function

Date Time

  • MINUTE function: Description, Usage, Syntax, Examples and Explanation
  • Convert Excel time to decimal seconds
  • Get month from date in Excel
  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Generate series of dates by weekends in Excel

Grouping

  • Categorize text with keywords in Excel
  • Map text to numbers in Excel
  • Group numbers at uneven intervals in Excel
  • Group numbers with VLOOKUP in Excel
  • If cell contains one of many things in Excel

General

  • Print Excel Sheet In Landscape Or Portrait
  • Currency vs Accounting Format in Excel
  • How to calculate percent of students absent in Excel
  • Advanced Number Formats in Excel
  • Count cells less than in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning