Convert Time to Decimal in Excel

The following examples convert a time to a decimal (i.e. to a number of hours, minutes or seconds) using two different methods.


Convert Time to Hours in Excel

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

Method 1: Simple Multiplication

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

 AB
1Time (hh:mm:ss)Hours (decimal)
202:30:00=A2 * 24

The formula in cell B2 of the above spreadsheet returns the value 2.5.

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

(The reason that this method works is because Excel times are internally stored as decimal values, with the value 1.0 used to represent 24 hours, and therefore, the value 1/24 used to represent 1 hour).

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.

 AB
1Time (hh:mm:ss)Hours (decimal)
202:30:00=HOUR(A2) + MINUTE(A2) / 60 + SECOND(A2) / 3600

Again, the formula in cell B2 of the above spreadsheet returns the value 2.5.


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 a number of minutes in Excel.

Method 1: Simple Multiplication

The simplest way to convert a time to a number of minutes is to multiply the time by 1440, which is the the number of minutes in one day:

 AB
1Time (hh:mm:ss)Minutes (decimal)
202:30:30=A2 * 1440

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

I.e. 2 hours 30 minutes and 30 seconds is equal to 150.5 minutes.

(The reason that this method works is because Excel times are internally stored as decimal values, with the value 1.0 used to represent 24 hours, and therefore, the value 1/1440 used to represent 1 minute).

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:

 AB
1Time (hh:mm:ss)Minutes (decimal)
202:30:30=HOUR(A2) * 60 + MINUTE(A2) + SECOND(A2) / 60

Again, the formula in cell B2 of the above spreadsheet returns the value 150.5.


Convert Time to Seconds in Excel

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

Method 1: Simple Multiplication

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

 AB
1Time (hh:mm:ss)Seconds (decimal)
202:30:30=A2 * 86400

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

I.e. 2 hours 30 minutes and 30 seconds is equal to 9030 seconds.

(The reason that this method works is because Excel times are internally stored as decimal values, with the value 1.0 used to represent 24 hours, and therefore, the value 1/86400 used to represent 1 second).

Method 2: Using the Excel Time Functions

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

 AB
1Time (hh:mm:ss)Seconds (decimal)
202:30:30=HOUR(A2) * 3600 + MINUTE(A2) * 60 + SECOND(A2)

Again, the formula in B2 of the above spreadsheet returns the value 9030.


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 you can use either of the following two methods:

Method 1 - Formatting Through the Ribbon Options

The easiest way to change cell formatting to the 'General' format is to select the cell(s) to be formatted and then select the General option from the drop-down menu in the ribbon. This is found in the 'Number' group on the Home tab (see below):

Excel Format Cells Drop Down Menu on Ribbon

Method 2 - Formatting Through the 'Format Cells' Dialog Box

This method can be used in all versions of Excel, including earlier versions that do not have the ribbon:

  • Right click on the cell(s) to be formatted;
  • Select the option Format Cells... to display the 'Format Cells' dialog box;
  • Ensure the Number tab of the dialog box is selected;
  • From the Category: list, select the option General and click OK.