How to Calculate, Format and Convert Time in Excel

Do you want to learn how to calculate, format, and convert time in Excel? In this article, we will guide you how to convert formats, calculate dates and times easily using built-in Excel formulas.

How to convert time difference between two time zones in Excel

Normally, when you calculate time zone difference with subtraction in Excel, you will get the result in hh:mm or hh:mm:ss format. To directly convert time difference to numbers like hours/minutes/seconds just after calculation, the methods in this article will help you a lot.

To convert time zone difference to hours

Select an empty cell (in this case, I select C2), enter the formula =(B2-A2)*24 into the Formula Bar and press Enter. Then drag the formula down to convert the time difference to hour format. As shown in the screenshot below

Convert time zone difference to minutes

To convert time difference into minutes, please use this formula = (B2-A2) * 1440 .

Convert time difference to seconds

This formula =(B2-A2)*86400 can help you directly convert time difference to seconds format as shown below.

Note : In the above formulas. A2 contains the start time and B2 contains the end time. You can change them as you need.

How to convert time from 12 hour to 24 hour format and vice versa in Excel

When you work on Excel in your daily work, you may use different time formats in different conditions, such as 12-hour format and 24-hour format as shown in the following screenshot. But, how can you convert the time format from 12-hour to 24-hour and vice versa in Excel?

The following formulas can help you convert time format from 12 hour to 24 hour and vice versa.

Convert time format from 12 hour to 24 hour:

Please enter this formula: =TEXT(A2,”[hh]:mm:ss”) into the blank cell where you want to output the result, then drag the fill handle down to the cell where you want to apply this formula, and all the time format has been converted from 12 hour to 24 hour, see screenshot:

Convert time format from 24 hour to 12 hour:

To convert time format from 24 hour to 12 hour, please enter this formula: =TEXT(A2,”hh:mm:ss AM/PM”) into an empty cell to find the result and drag the fill handle down to the cell where you want to contain this formula and all 24 hour time format cells have been converted to 12 hour format, see screenshot:

How to convert time series to time in Excel

For example, you have a list of text strings in a worksheet and now you need to convert these text strings to time or date and time, how can you solve it? Here I will introduce some tricks for you to quickly convert text strings to time in Excel.

Convert text string to time

To convert a text string to a time, you can use some of the following formulas

1. Select a cell and enter this formula =TIMEVALUE(LEFT(A1,LEN(A1)-2)&”:”&RIGHT(A1,2)) (A1 is the text string you need to convert to time) into it, and press Enter . If needed, you can drag this formula down to the cells you need

2. Then, right-click on these selected cells and choose Format Cells from the context menu, then in the Format Cells dialog box, choose Time from the Category list on the Number tab, and then choose the time style you need.

3. Click OK , now the text strings are converted to time.

Note

• This formula may not work correctly when the text string includes seconds, such as 120158.
• If your text string format is 120456P, you can use this formula =TEXT(-(LEFT(A1,LEN(A1)-1)),”0\:00\:00″) + ((RIGHT(A1),1)=”P”)/2) , then format the cells as the 12 hour clock you need. See screenshot:

Convert text string to date and time

If your text string includes a date and time such as 20141212 0312, you can use a slightly longer formula to solve it.

1. In the blank cell, enter this formula =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)) + TIME(MID(A1,10,2),RIGHT(A1,2),0) , and then press Enter key, if you need, you can apply this formula to a range.

2. Then right-click on the selected cells and select Format Cells from the context menu, then in the Format Cells dialog box, select Date from the Category list and choose the type you need from the right section.

3. Click OK . Now you can see the text string converted to date and time.

How to convert time format in Excel with formula

Suppose you have a range of times or text in the format [HH:MM:SS] in Excel and now you want to convert them to decimal hours, minutes or seconds (for example convert from 10:34:05 to 10.57 ). How can you quickly solve it? Here this tutorial will introduce some tricks to handle this task.

Convert time to decimal hours/minutes/seconds with formula

In Excel, you can use several formulas to convert [hh:mm:ss] to decimal hours, minutes, or seconds.

1. Select a cell and enter the formula =A1*24 into that cell and press Enter to get the result

2. Right-click on the selected range and click Format Cells from the context menu. And in the popping dialog box, select Number from the Category list, and specify the decimal places you need.

3. Click OK . Now each time has been converted to decimal hours at the same time.

Note:

• You can also use this formula to convert time to hours = HOUR(A1) + MINUTE(A1) / 60 + SECOND(A1) / 3600 .
• If you want to convert time to decimal minutes, use =A1 * 24 * 60 or =HOUR(A1) * 60 + MINUTE(A1) + SECOND(A1) / 60 .
• If you want to convert time to decimal seconds, use =A1 * 24 * 60 * 60 or =HOUR(A1) * 3600 + MINUTE(A1) * 60 + SECOND(A1) .

Convert time to decimal in 24 hours with formula

Suppose, I have a list of cells containing time over 24 hours, now, I want to convert the subject time over 24 hours to decimal hours as shown below. How can you solve this task in Excel quickly and easily?

Here is a formula that can help you convert 24 hour time to decimal as easily as possible.

Enter this formula: =IF(A2>TIME(24,0,0),TEXT(A2, “[h]:mm:ss”)*24,A2) into an empty cell where you want to output the result and then drag down the cells where you want to apply this formula and all times over 24 hours have been converted to decimal hours, see screenshot:

How to convert time to integer hours or minutes with formula in excel

Generally, when you convert time to decimal hours or minutes with the formula = cell * 24 or cell * 1440, the result will be decimal numbers. But sometimes, you just want to convert time to integer hours or minutes like the screenshot below, how can you solve it in Excel?

With the INT formula , you can convert time to an integer number of hours or minutes.

Select the cell where you want to put the result, select the formula you need from below:

Convert to integer hours = INT(A2 * 24)

Convert to integer minutes = INT(A2 * 1440)

Then press Enter to get the result. If you need to, drag the fill handle down to fill the cells with formulas.

Hopefully, through this article, you have learned some useful tricks to work with time in Excel spreadsheets. Besides, to not miss other office computer tips and tricks, join us today.