Explanation
In the Excel time system, one 24-hour day is equal to 1. This means times and hours are fractional values of 1, as shown in the table below:
| Hours | Time | Fraction | Value |
|---|---|---|---|
| 1 | 1:00 AM | 1/24 | 0.04167 |
| 3 | 3:00 AM | 3/24 | 0.125 |
| 6 | 6:00 AM | 6/24 | 0.25 |
| 4 | 4:00 AM | 4/24 | 0.167 |
| 8 | 8:00 AM | 8/24 | 0.333 |
| 12 | 12:00 PM | 12/24 | 0.5 |
| 18 | 6:00 PM | 18/24 | 0.75 |
| 21 | 9:00 PM | 21/24 | 0.875 |
Because each hour can be represented as 1/24, you can convert an Excel time into decimal hours by multiplying the value by 24. For example, with the time value 6:00 cell A1, you can visualize the conversion like this:
=A1*24
=(6/24)*24
=0.25*24
=6
Format result as number
When you multiply a time value by 24, Excel may automatically format the result using a time format like h:mm, which will display the value incorrectly. To display the result as a regular number, apply the General or Number format.
Related formulas
Calculate hours between two times
In this example, the goal is to calculate the number of hours between two times. This basic problem comes up frequently when tracking time and may be described in various ways: Calculate working time in hours. Calculate the duration of an activity in hours. Calculate the hours needed for a task...
Convert Excel time to decimal minutes
In the Excel time system, one 24-hour day is equal to 1. This means times and hours are fractional values of 1, as shown in the table below: Hours Time Fraction Value 1 1:00 AM 1/24 0.04167 3 3:00 AM 3/24 0.125 6 6:00 AM 6/24 0.25 4 4:00 AM 4/24 0.167 8 8:00 AM 8/24 0.333 12 12:00 PM 12/24 0.5 18 6...
Convert decimal hours to Excel time
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...
Convert decimal minutes to Excel time
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 Minutes Value Time 1 1/24 60 0.04167 1:00 3 3/24 180 0.125 3:00 6 6/24 360 0.25 6:00 4 4/24 240 0.167 4:00 8 8/24 480 0.333 8:00 12 12/24 720 0.5 12:00 18...
Convert decimal seconds to Excel time
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 Minutes Seconds Value Time 1 1/24 60 3600 0.04167 1:00 3 3/24 180 10800 0.125 3:00 6 6/24 360 21600 0.25 6:00 12 12/24 720 43200 0.5 12:00 18 18/24 1080...
Calculate hours between two times
In this example, the goal is to calculate the number of hours between two times. This basic problem comes up frequently when tracking time and may be described in various ways: Calculate working time in hours. Calculate the duration of an activity in hours. Calculate the hours needed for a task...
Add decimal hours to time
In this example, the goal is to add hours in decimal format (i.e. 1, 2, 3, etc.) to an existing Excel time. The complication is that Excel stores time as fractional values. The number 0.25 makes sense when you consider that 6 hours is one-quarter of a day, and a day in Excel equals 1. But it isn't...
Timesheet overtime calculation formula
Note: it's important to understand that Excel deals with time as fractions of a day . So, 12:00 PM is .5, 6:00 AM is .25, 6 PM is .75, and so on. This works fine for standard time and date calculations, but in many cases you'll want to convert times to decimal hours to make other calculations more...
Get days, hours, and minutes between dates
Most of the work in this formula is done by the TEXT function, which applies a custom number format for hours and minutes to a value created by subtracting the start date from the end date. TEXT(C5-B5,"h"" hrs ""m"" mins """) This is an example of embedding text into a custom number format, and...
Related videos
How to use number formatting in Excel
In this lesson, we'll look at the Number format called "Number." The Number format provides three options: the ability to set a fixed number of decimal places, an option to use commas to separate thousandths, and several ways to display negative numbers. Let's take a look. To start off, let's copy...
