[ad_1]
While Excel offers many features and functions for working with times, you may want your times formatted as decimals instead. You might need them for use in other formulas, for a timesheet, or for your own preference.
Here are a few ways to convert time to decimals in Excel. You can use whichever method you’re most comfortable with or works best for you.
Use the Time and Decimal Formats
Before you convert the times and decimals in your sheet, make sure that 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” in the Number drop-down box on the Home tab. If you plan to convert hours, minutes, and seconds, it’s best to use the Format Cells feature. Select the cell or range, right-click, and pick “Format Cells.”
Choose the Number tab and pick “Time” on the left. Then, pick the hours:minutes:seconds option on the right as shown below and click “OK.”
For decimals, format your result cells as numbers also using the Format Cells feature. Right-click the cell(s) and pick “Format Cells.”
Choose the Number tab and pick “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 your result, you can use the Increase and Decrease Decimal buttons in the Number section of the Home tab to adjust the appearance. This lets you display 624.00 as 624.
You’re then ready to start converting.
Convert Using Multiplication
If you’re not fond of using functions and building the accompanying formulas in Excel, you can convert time to decimals with simple multiplication.
RELATED: 12 Basic Excel Functions Everybody Should Know
You’ll 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 obtain the hours as a decimal, we multiply that value by 24, for 24 hours in a day.
=A2*24
To obtain the decimal for minutes and seconds using that same cell value, you would 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 receive the number results for our time conversions.
Convert Using the CONVERT Function
Another option for getting decimals for times in Excel is with the CONVERT function. This versatile function lets you convert times, measurements, weights, and more.
RELATED: How to Add or Subtract Times in Microsoft Excel
The syntax for the function is CONVERT(reference, from, to) where you’ll use all three arguments. For the from argument, you’ll 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 argument.
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
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 the cell A2 and the results are added together. 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
Similar to the formula for hours, you add the result of the functions together. The HOUR portion is multiplied by 60 for 60 minutes in an hour and the SECOND portion is divided by 60 for 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 3,600 for 3,600 seconds in an hour and the MINUTE portion is multiplied 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, it might be your preferred one.
Converting time to decimals in Excel sounds more difficult than it is. And, when you pick the above method that you like best, you’ll be converting in no time.
[ad_2]
Source link
