Convert Time to Decimal in Excel

Related Page:
Seconds To Time In Excel

The following examples convert time to decimal in Excel, using two different methods.

The first method uses the fact that Excel stores times as decimals, with the number 0 equal to the time 00:00:00, the number 0.25 equal to the time 06:00:00, the number 0.5 equal to the time 12:00:00, etc. Because of this system, a time can be converted to hours, minutes or seconds by multiplying it by the number of hours, minutes or seconds in one day.

The second method that we use to convert a time to a decimal uses the Excel Hour, Minute, and Second functions to extract the hours, minutes and seconds from an Excel time.


Convert Time to Hours in Excel

The following spreadsheets show two different formulas that can be used to convert a time to hours in Excel.

The numerical value returned from these formulas is a decimal, which includes an integer portion, representing the number of whole hours, and a decimal portion, representing the minutes and seconds.


Method 1: Simple Multiplication

The simplest formula is shown below. In this case, the time is converted to hours by simply multiplying it by 24 (the number of hours in one day).

  A B
1 Time (hh:mm:ss) Hours (decimal)
2 02:30:00 =A2 * 24

For the time 02:30:00, in cell A2 of the example spreadsheet above, the formula returns the value 2.5.

Ie. 2 hours 30 minutes and 0 seconds is equal to 2.5 hours.


Method 2: Using the Excel Time Functions

The second formula that can be used to provide the same result uses the Excel Hour, Minute, and Second functions. Although this formula is much longer than the simple multiplication shown above, some people prefer it, as it doesn't rely on the user having an understanding Excel's time system. This formula is shown in the spreadsheet below:

  A B
1 Time (hh:mm:ss) Hours (decimal)
2 02:30:00 =HOUR(A2) + MINUTE(A2) / 60 + SECOND(A2) / 3600


Convert Time to Minutes in Excel

Cell B2 of the spreadsheets below show the two formulas that can be used to convert a time to minutes in Excel.

The numerical value returned in this case, is a decimal, that includes an integer portion, representing the number of whole minutes, and a decimal portion, representing the seconds.


Method 1: Simple Multiplication

To convert a time to minutes, the time is multiplied by 1440, which is the the number of minutes in one day:

  A B
1 Time (hh:mm:ss) Minutes (decimal)
2 02:30:30 =A2 * 1440

For the time 02:30:30, in cell A2 of the above spreadsheet, the formula returns the value 150.5.

Ie. 2 hours 30 minutes and 30 seconds is equal to 150.5 minutes.


Method 2: Using the Excel Time Functions

The same result can also be obtained using the Excel Hour, Minute and Second functions, as shown in the spreadsheet below:

  A B
1 Time (hh:mm:ss) Minutes (decimal)
2 02:30:30 =HOUR(A2) * 60 + MINUTE(A2) + SECOND(A2) / 60


Convert Time to Seconds in Excel

The spreadsheets below show the formulas that can be used to convert a time to seconds in Excel.


Method 1: Simple Multiplication

The easiest way is to simply multiply the time by 86400, which is the the number of seconds in one day:

  A B
1 Time (hh:mm:ss) Seconds (decimal)
2 02:30:30 =A2 * 86400

For the time 02:30:30, in cell A2 of the above spreadsheet, the formula returns the value 9030.

Ie. 2 hours 30 minutes and 30 seconds is equal to 9030 seconds.


Method 2: Using the Excel Time Functions

The same result can be obtained using the Excel Hour, Minute and Second functions, as shown below:

  A B
1 Time (hh:mm:ss) Seconds (decimal)
2 02:30:30 =HOUR(A2) * 3600 + MINUTE(A2) * 60 + SECOND(A2)


Formatting the Result

When you convert a time to a decimal, the cell containing the result may have the wrong formatting (e.g. the result may be displayed as a time, instead of a decimal).

In this case, you will need to format the cell to have the Excel 'General' format. To do this:

  • Right click on the cell(s) to be formatted
  • Select the option Format Cells...
  • Ensure the Number tab is selected in the window that pops up
  • Select the option General from the list of Categories and click OK
Return to the Excel-Formulas Page

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net