[ad_1]
Although Excel offers many features and functions for working with times, you may want your times to be formatted as decimals. You may need them to use in other formulas, for a time sheet, or for your own preferences.
Here are some ways to convert time to decimals in Excel. You can use the method that you feel most comfortable with or that works best for you.
Use time and decimal formats
Before you convert the hours and decimals in your sheet, make sure the cells are formatted as such.
RELATED: How to use the accounting number format in Microsoft Excel
For time, you can do this by selecting the cell and choosing “Time” from the Number dropdown box on the Home tab. If you plan to convert hours, minutes, Y seconds, it is better to use the Format Cells function. Select the cell or range, right click and select “Format Cells”.
Choose the Number tab and choose “Time” on the left. Then choose the hours:minutes:seconds option on the right as shown below and click “OK”.
For decimals, format your result cells as numbers using the Format Cells function as well. Right-click on the cell(s) and select “Format Cells.”
Choose the Number tab and choose “Number” on the left. Set the number of decimal places to 2 on the right and click “OK”.
If you end up with a whole number as a result, you can use the Increase and Decrease Decimals buttons in the Number section of the Home tab to adjust the appearance. This allows you to display 624.00 as 624.
Then you are ready to start converting.
Convert using multiplication
If you don’t like using functions and creating their accompanying formulas in Excel, you can convert time to decimals with simple multiplication.
RELATED: 12 Basic Excel Functions Everyone Should Know
It will 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 that value by 24, times 24 hours in a day.
=A2*24
To get the decimal of minutes and seconds using the same cell value, you would multiply by the number of minutes (1440) and then the number of seconds (86400) in a day as follows:
=A2*1440
=A2*86400
As you can see, we receive the numerical results of our time conversions.
Convert using the CONVERT function
Another option to obtain decimals of times in Excel is with the CONVERT function. This versatile feature allows you to convert times, measurements, weights, and more.
RELATED: How to Add or Subtract Times in Microsoft Excel
The syntax of the function is CONVERT(reference, from, to) where you will use all three arguments. For him from argument, it will use “day”.
To convert a time in cell A2 to hours, you would use this formula:
=CONVERT(A2,"day","hr")
You can also use “d” instead of “day” for the from plot.
To convert that same time to minutes and seconds, you would use these formulas:
=CONVERT(A2,"day","mn")
=CONVERT(A2,"day","sec")
You can also use “min” instead of “mn” for minutes and “s” instead of “sec” for seconds.
Convert using time function formulas
This last method uses Excel’s time functions. Although the formulas are much longer than the previous ones, it is another option that you may prefer. Here, you will use the HOUR, MINUTE, and SECOND functions with a combination of calculations.
RELATED: 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
Each function references cell A2 and the results are added together. The MINUTES portion is divided by 60 times 60 minutes in an hour and the SECONDS portion is divided by 3,600 times 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
Similar to the formula for hours, you add the result of the functions. The HOUR portion is multiplied by 60 times 60 minutes in an hour and the SECOND portion is divided by 60 times 60 seconds in a minute.
To convert that time to seconds, you would use this formula:
=HOUR(A1)*3600+MINUTE(A2)*60+SECOND(A2)
With this formula, the HOUR portion is multiplied by 3600 times 3600 seconds in an hour and the MINUTE portion is multiplied by 60 times 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, it might be your preference.
Converting time to decimals in Excel sounds more difficult than it is. And, when you choose the method above that you like best, you’ll be turning in no time. weather.
[ad_2]
