How to convert time in Excel

Excel provides users with many different types of Excel basic functions, divided into separate groups to cater to each content required to perform. To convert time into hours, minutes and seconds we can use the operation to convert the time value into decimal numbers, or use the appropriate function to convert the values. Each type of conversion will perform differently, with different benefits. The following article will guide you how to convert time in Excel.

1. The DAYS function, how to use the function to calculate the number of days between two time points in Excel
2. How to correct a #VALUE date error in Excel
3. How to split date, month and year columns into 3 different columns in Excel
4. How to convert a serial number to a date in Excel

1. How to convert hours with Excel math

We will have the time table for example as below. We will use math to convert hours, minutes and seconds. The rule of thumb is that there will be 24 hours a day, 1 hour = 60 minutes, 1 minute = 60 seconds.

Step 1:

In cell C2 of the Convert to hour column , the user enters the calculation = + C2 * 24 . + Sign before to ensure a positive value for the result, you use or remove it.

Step 2:

User results are the first hour value box. You drag the resulting cell down to the remaining cells to be column hour values ​​as shown.

Step 3:

Results show as decimal but not truncated. If you want to shorten the value , highlight the column and then right-click and select Format Cells . Then display the panel as shown, click the Number group, then look to the right, convert Decimal places to 2, then click OK.

In case you want the result to be integer , not in decimal form, combine it with INT .

The result shown is the integer value as shown below.

Step 4:

We continue to convert the minute value with the input operation as shown below.

The result is a complete minute value as shown below.

Step 5:

Proceed to the second value column with the input operation as shown.

The result is column value as below.

2. Change the time with the Convert function in Excel

The Convert function in Excel is used to convert measurement values. The formula for the Convert function is = CONVERT (number, from_unit, to_unit). Inside:

1. Number: is the value to be converted.
2. From_Unit: unit to convert.
3. To_Unit: unit conversion.

The values ​​of time units are day (day), hr (hour), mn (minute), sec (second).

Step 1:

To change the time to an hour value, enter the formula = CONVERT (B2, "day", "hr") , the default from_unit value is "day" .

When you press Enter, the result is the correct hour value with the calculation.

Step 2:

With the column converted to minutes, enter the formula = CONVERT (B2, "day", "mn") and press Enter and produce the following result.

Step 3:

If you want to change the seconds value enter the formula = CONVERT (B2, "day", "sec") and then drag the first result cell down to the remaining cells.

3. Transfer time using the Hour, Minute, Second functions

We will use these 3 functions to calculate the values ​​for each component of time and then add them together to produce the result of minutes and seconds. The function syntax is:

1. HOUR (serial_number)
2. MINUTE (serial_number)
3. SECOND (serial_number)

Where serial_number is the number to be converted.

Step 1:

In the first cell in the number of hours, enter the formula = HOUR (B2) and press Enter. Displaying the results we scroll down to the remaining cells

For a cell in the number of minutes column you will change to the formula = MINUTE (B2) and also press Enter.

The remaining column enters the function formula as = SECOND (B2) .

Step 2:

Next in the value box the final result we will do the addition.The formula will be equal to the number of hours * 24 + minutes + seconds / 60 . Apply to the table we have the formula is E2 = C2 * 60 + D2 + E2 / 60.

The results are displayed in the first box, scrolling down to the remaining cells is done.

So we have different ways to convert time into different values. Note that when converting time by calculation, you need to format the cell as General, then convert to Number if you want to collapse the results.

I wish you successful implementation!