How to Convert Time to Decimals in Microsoft Excel

Microsoft Excel logo on a green background

Although Excel offers many features and functions for working with times, you may want your times formatted as decimals instead. You may need them for use in other formulas, for a timesheet, or for your own preferences.

Here are a few ways to convert time to decimal in Excel. You can use whichever method you are most comfortable with or that works best for you.

Use the time and decimal formats

Before you convert the times and decimals in your sheet, make sure the cells are formatted as such.

TIED TOGETHER: How to use accounting number format in Microsoft Excel

For time, you can do this by selecting the cell and choosing “Time” in the “Number” drop-down box on the “Home” tab. If you plan hours, minutes, and Seconds it is best to use the “Format Cells” function. Select the cell or range, right-click and choose Format Cells.

Format Cells in the context menu

Select the “Number” tab and select “Time” on the left. Then select the Hours:Minutes:Seconds option on the right side as shown below and click “OK”.

Format cells as time in Excel

For decimal numbers, also format your result cells as numbers using the Format Cells function. Right-click the cell(s) and choose Format Cells.

Select the “Number” tab and select “Number” on the left. On the right, set the number of decimal places to 2 and click OK.

Format cells as numbers in Excel

If the result is an integer, you can use the Increase Decimal and Decrease Decimal buttons in the Number section of the Home tab to customize the appearance. This allows you to display 624.00 as 624.

Read  How to Get All Endings

You can then start the conversion.

Convert with multiplication

If you don’t like using functions and creating their associated formulas in Excel, you can convert time to decimal numbers using simple multiplication.

TIED TOGETHER: 12 basic Excel functions everyone should know

You multiply the cell containing the time by the number of hours, minutes, or seconds in a day. Here we have our time in cell A2. To get the hours as a decimal, we multiply this value by 24 for 24 hours in a day.

=A2*24

To get the decimal places for minutes and seconds with the same cell value, multiply by the number of minutes (1,440) and then the number of seconds (86,400) in a day, as follows:

=A2*1440
=A2*86400

As you can see, we get the numerical results for our time conversions.

multiplication conversions

Convert with the CONVERT function

Another way to get decimal numbers for times in Excel is to use the CONVERT function. Convert times, measurements, weights and more with this versatile function.

TIED TOGETHER: How to add or subtract times in Microsoft Excel

The syntax for the function is CONVERT(reference, from, to) where you use all three arguments. For the from Argument use “day”.

To convert a time in cell A2 to hours, you would use this formula:

=CONVERT(A2,"day","hr")

CONVERT function formula for hours

You can also use “d” instead of “day” for the from Fight.

To convert the same time to minutes and seconds you would use these formulas:

=CONVERT(A2,"day","mn")
=CONVERT(A2,"day","sec")

CONVERT function formula for minutes

You can also use “min” instead of “mn” for minutes and “s” instead of “sec” for seconds.

Read  How to delete cookies on Android

Convert with time function formulas

This last method uses Excel’s time functions. Although the formulas are much longer than the ones above, this is another option that you may prefer. Here you use the HOUR, MINUTE and SECOND functions with a combination of calculations.

TIED TOGETHER: 13 Microsoft Excel Date and Time Functions You Should Know

To convert the time in cell A2 to hours, you would use this formula:

=HOUR(A2)+MINUTE(A2)/60+SECOND(A2)/3600

Time function formula for hours

Each function refers to cell A2 and the results are added. The MINUTE portion is divided by 60 for 60 minutes in an hour, and the SECOND portion is divided by 3,600 for 3,600 seconds in an hour.

To convert the same time to minutes, you would use this formula:

=HOUR(A2)*60+MINUTE(A2)+SECOND(A2)/60

Time function formula for minutes

Similar to the hourly formula, you add the result of the functions together. The HOUR part is multiplied by 60 for 60 minutes in one hour, and the SECOND part is divided by 60 for 60 seconds in one minute.

To convert that time to seconds you would use this formula:

=HOUR(A1)*3600+MINUTE(A2)*60+SECOND(A2)

Time function formula for seconds

This formula multiplies the HOUR portion by 3600 for 3600 seconds in an hour and the MINUTE portion by 60 for 60 seconds in a minute.

As you can see, this last method requires more effort, but if you’re familiar with using time functions in Excel, this might be your preferred method.

Converting time to decimal in Excel sounds harder than it is. And if you choose the above method which you like the most, you will convert in No time.

Leave a Comment

Your email address will not be published.