Excel's Time Conversion Tool: Decimal, Hour, Minute, Second
The tutorial shows several methods for making the Excel time-to-decimal conversion. Formulas to convert text to time and vice versa, as well as time to hours, minutes, and seconds, can be found here.
Because times are stored as numbers in Microsoft Excel, you can easily convert between hours, minutes, and seconds.
Altering the cell format or employing arithmetic calculations or Excel time functions like HOUR, MINUTE, and SECOND are the two most common ways to convert time to decimal in Excel. The first method will be explained in greater depth below, and the second method will be illustrated through formula examples.
A Decimal to Fractional Time Conversion in Excel
Time values can be converted to decimal numbers using either arithmetic or the CONVERT function, or a combination of three other Time functions.
Multiplying the original time value by the total number of hours, seconds, or minutes in a day is the quickest way to get a decimal representation of the time in Excel:
- Multiply the time by 24 to get the number of hours it corresponds to in a day.
- Time can be converted to minutes by multiplying it by 1440 (24 hours multiplied by 60 minutes).
- Simply multiply the time by 86400 to get the number of seconds it is in a day (24 * 60 * 60).
What follows are detailed instructions for the remaining Excel-supported approaches to converting times to decimal numbers.
Instructions for doing the Excel time-to-hours conversion
In this section, you'll see three different ways to convert hours from the 24-hour format (hh:mm:ss) to a decimal number.
1. Arithmetic Formula
You are already familiar with Excel's built-in function for quickly converting a time value to a number of hours: multiplying by 24. e according to the time of day:
When the time value, A2, is known, we can say
The above formula can be embedded in the INT function to eliminate the fractional part and yield the total number of worked hours:
Function CONVERT (formula 2)
If you prefer a different method, you can use the following Convert formula to make the "time > hours" change:
Formula: =CONVERT(A2, "day", hr)
Time functions of an hour, minute, and second (Formula 3)
As a final resort, you can employ a slightly more involved formula, the logic of which is, nonetheless, self-evident. First, we use the HOUR, MINUTE, and SECOND functions to separate the time into its component parts; then, we divide each minute's value by 60 (there are 60 minutes in an hour) and each second's value by 3600 (there are 3600 seconds in an hour), and finally, we add the two numbers together.
=(A2:H) +(A2:M)/(A2:60) +(A2:S)/3600
Converting Hours to Minutes in Excel
Minutes can be converted from the 12-hour format to the decimal system using the same three methods.
Method 1: Simple arithmetic formula
Multiply the time by 1440, the sum of the minutes in a day (24 hours * 60 minutes = 1440):
Like in the preceding example, the INT function can be used to return the total number of completed minutes.
Below is a screenshot showing the final outcome:
Method 2: The CONVERT Formula
Time can be converted from days to minutes using the CONVERT(number, from_unit, to_unit) function by passing in "day" as the "from" unit and "mn" as the "to" unit.
Equality: =CONVERT(A2, "day", mn)
Third Formula: HOUR, MINUTE, and SECOND
Another way to calculate minutes is to divide 60 by the number of seconds in an hour and then multiply the result by 60.
=60 * HOUR(A2) + 60 * MINUTE(A2) + 60 * SECOND(A2)/60
Excel's Seconds-to-Hours Conversion Formula
Likewise, Excel provides a method for converting time to a cumulative number of seconds.
1 - Arithmetic calculation formula
The number of seconds in a day can be calculated by multiplying the given time by 24, 60, and 60, yielding 86,400:
Use of the CONVERT function (formula 2)
Simply change the "day" unit to "sec" in the following formula, and the results will be the same as in the previous examples.
As an example: =CONVERT(A2, "day", "sec")
Three functions for measuring time in hours, minutes, and seconds
To replicate the results of the first two examples, use the HOUR, MINUTE, and SECOND functions (at this point, I don't think an explanation of the formula's logic is necessary).
the formula: =HOUR(A2)*3600 + MINUTE(A2)*60 + SECOND(A2)
- If any of the above formulas produce a time-based result, you can convert it to a numerical value by setting the cell's format to "General."
- Applying the General format to a cell will convert the time into a decimal that Excel will use internally. When using this method, the value 23:59:59 will be rounded down to 0. 99999, 06:00 AM to 0 25 and from 12 noon to midnight 5 If the converted number has a non-zero integer component, that means the cell contains both date and time information.
You may find that a single cell in your Excel worksheet contains both a date and a time, but you'd prefer to have them in separate cells.
Keep in mind that the INT function, which rounds the cell value down to the nearest integer, can be used to extract the date value by keeping in mind that the date value is stored as a whole part and the time value is stored as a fractional part of a decimal number in Excel's internal system.
The following formula will separate the original dates and times that were in column A:
To get just the time from a date and time value, subtract the date returned by the above formula.
The input date and time values are located in column A, and the dates generated by the INT function are located in column B.
To avoid having time values associated with the split dates (in case you ever decide to get rid of the date column, for instance), you can use the MOD formula below, which only uses the original data as its reference.
Tip Ensure that the newly created columns are formatted as Date and Time, respectively, if the separated date and time values are not being displayed correctly.
Use Excel's built-in functionality to separate the two elements of time and date Using the HOUR, MINUTE, and SECOND functions, as shown in How to get hours, minutes, and seconds from a timestamp, you can further separate the time components into their own columns.
In Excel, how to correctly spell "time"
Time may need to be expressed as "# days, # hours, # minutes, and # seconds" It's a good thing you're familiar with the formula's components:
- Get the number of days using the INT function;
- Gather the hours, minutes, and seconds using the HOUR, MINUTE, and SECOND functions, and
- Combine everything into one formula.
Struggling to come up with the right formula for your worksheet? Use the following as a simple example to understand
Let's say you have the NOW() function return the current date and time in cell B1, and the dates of upcoming events in column B beginning in cell B4.
Simple time difference calculations can be done by plugging the following formula into a calculator: =B4 - $B$1. There is, of course, nothing stopping you from simply using =B4-NOW() to subtract today's date and time.
Let's create a countdown timer that will display the number of days, hours, minutes, and seconds until each event occurs.
Here is the formula you need to enter in cell D4:
Time Period Equals: =INT(C4) & " days, " & HOUR(C4) & " hours, " & MINUTE(C4) & " minutes, and " & SECOND(C4) & " seconds"
To remove null data, such as that found in cells D6 and D7 above, insert the appropriate IF statements:
Date and time are calculated as follows: =IF(INT(C4)>0, INT(C4)&" days, ","") & IF(HOUR(C4)>0, HOUR(C4)&" hours, ","") & IF(MINUTE(C4)>0, MINUTE(C4)&" minutes and ","") & IF(SECOND(C4)>0, SECOND(C4)&" seconds","")
Those zeros are now history.
Note Any time a negative number is used in either of the preceding formulas, the #NUM There will be an error. It's possible that this will occur if we subtract a longer time from a shorter one.
Applying the following custom time format to the cell provides yet another option for representing time in Excel in the form of words: d "day," h "hours," m "minutes," and s "seconds." There is no need for complicated calculations or formulas. Check out the Excel tutorial on making your own time format for more info.
Using Excel's text-to-time converter
Time values formatted as text are a common source of errors in time-based formulas and calculations. Excel's TIMEVALUE function is the quickest way to convert text to time.
There is only one argument for Excel's TIMEVALUE function.
Time_text is a string that can be in any of Excel's supported time formats. For instance:
When: =TIMEVALUE("6:20 PM")
In this example, we'll use =TIMEVALUE("6-Jan-2015 6:20 PM")
To convert a string of text into a time value, type =TIMEVALUE(A2) in cell A2.
As can be seen, the outcomes of formulas involving cell references and their corresponding text strings are identical. Time strings (text values) in cells A2 and A6 are aligned to the left, while converted time values in column D are aligned to the right.
Use Excel's time-to-text function
Let's say you've got a bunch of times in Excel with the format "8:30:00 AM" and you want to turn them into text. Changing the cell format to TEXT would result in the time values reverting to their underlying numeric representation, rendering the change ineffective. As an illustration, the time 8:30:00 AM will become the decimal value 0 354166666666667
How do you save the time in your cells when converting them to text? As an example, you can use the TEXT function to transform a numerical value into text using the display formatting you specify.
See below for an example of one of many other possible layouts:
A Step-by-Step Guide to Excel's Time Format Conversion
Consider a set of numbers, say 1, 2, 3. If you have any numbers greater than 5 and need to convert them to a time format, such as 1:00:00, 2:00:00, or 3:30 AM, then you can do so as follows:
- There are 24 hours in a day, so divide the figures by 24. A simple formula could be =A2/24.
- You can format the cells that contain the formula result by selecting them, right-clicking, and then choosing Format Cells from the resulting menu (or by pressing Ctrl 1 In either case, the Format Cells dialog will pop up; from the left pane, select Time from the Category drop-down, and from the right pane, select the desired format type. For further information, see How to Format Time in Excel.
Follow these rules if you need to display a duration that is longer than 24 hours, 60 minutes, and 60 seconds.
So long, at least for now You are welcome to download the sample workbook provided below to get hands-on experience with the formulas used in this article.
To learn more about how to use Excel's time-calculating functions, see the resources listed below. Please come back next week; I appreciate your time.
Instructional handout available for download
Instances of Time Conversion in Excel ( xlsx file)
The Excel Times' Tutorials
If you're thinking about taking a trip to the United States, you might consider exchanging some of your money into U.S. dollars, which is the official currency of the country. The international symbol for the currency is USD.USD is also the official currency in a few other countries, including Ecuador
If you're considering a journey to the United States, it might be beneficial to convert some of your money into U.S. dollars, which is the official currency of the country. The internationally recognized symbol for this currency is USD.Additionally, USD serves as the official currency in Ecuador and El
Utilize our inch-to-fraction calculator to effortlessly perform conversions between inch fractions, decimal values, metric measurements, and feet. Effective Techniques for Calculating Inch FractionsInches can be represented as fractions or decimals. When dealing with inch fractions, it is vital to
Please enter the necessary values below to convert kilowatts [kW] to British thermal units per hour [Btu/h], or the other way around.Description: A kilowatt (symbol: kW) is a unit of power within the International System of Units (SI). The watt, after the Scottish inventor James Watt, serves as the base