To convert a valid Excel time into decimal hours, simply multiply by 24. In the example shown, the formula in C6 is:
which returns a value of 1.
How this formula works
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:
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:
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.
To convert a valid Excel time into decimal minutes, you can multiply by 1440. In the example shown, the formula in C6 is: = B6 * 1440 which returns a value of 30. How this formula works In the Excel time system, one 24-hour day is equal to 1. This...
To convert hours in decimal format to a valid Excel time , divide by 24. 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...
To convert minutes in decimal format to a proper Excel time, divide by 1440. In the example shown, the formula in C6 is: = B6 / 1440 Because B6 contains 60 (representing 360 minutes) the result is 60/1440 = 0.04167, since 60 minutes = 1 hour = 1/24...
To convert seconds in decimal format to a proper Excel time, divide by 86400. In the example shown, the formula in C6 is: = B6 / 86400 To display the result as time, apply a time format. Column D shows the same result formatted with [h]:mm. How this...
To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary (...
To add a given number of hours to a time, you can add hours divided by 24, or use the TIME function. In the example shown, the formula in D5 is: = B5 + ( C5 / 24 ) Note: make sure results are formatted as time. How this formula works Times in Excel...
To calculate overtime and pay associated with overtime, you can use the formulas explained on this page. In formula in cell I5 is: = ( F5 * H5 ) + ( G5 * H5 * 1.5 ) How this formula works Note: it's important to understand that Excel deals with time...
To calculate and display the days, hours, and minutes between two dates, you can use the TEXT function with a little help from the INT function . In the example shown, the formula in D5 is: = INT ( C5 - B5 ) & " days " & TEXT ( C5...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.