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.

How to convert time in Excel Picture 1

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.

How to convert time in Excel Picture 2

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.

How to convert time in Excel Picture 3

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.

How to convert time in Excel Picture 4

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

How to convert time in Excel Picture 5

The result shown is the integer value as shown below.

How to convert time in Excel Picture 6

Step 4:

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

How to convert time in Excel Picture 7

The result is a complete minute value as shown below.

How to convert time in Excel Picture 8

Step 5:

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

How to convert time in Excel Picture 9

The result is column value as below.

How to convert time in Excel Picture 10

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" .

How to convert time in Excel Picture 11

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

How to convert time in Excel Picture 12

Step 2:

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

How to convert time in Excel Picture 13

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.

How to convert time in Excel Picture 14

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

How to convert time in Excel Picture 15

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

How to convert time in Excel Picture 16

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

How to convert time in Excel Picture 17

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.

How to convert time in Excel Picture 18

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

How to convert time in Excel Picture 19

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!