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:

=A2*24

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:

=INT(A2*24)

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):

=A2*1440

Like in the preceding example, the INT function can be used to return the total number of completed minutes.

=INT(A2*1440)

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:

=A2*86400

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)

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:

=INT(A2)

To get just the time from a date and time value, subtract the date returned by the above formula.

=A2-B2

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.

=MOD(A2,1)

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.

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.

TIMEVALUE(time_text)

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.

=TEXT($A2,"h:mm:ss")

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:

1. There are 24 hours in a day, so divide the figures by 24. A simple formula could be =A2/24.
2. 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