Time function, convert 24h to 12h or vice versa

In today’s Excel column, I will share with you how to calculate the total time in hours, minutes, seconds and change the 24h time format to 12h and vice versa using the TEXT function.

Okay, now let’s get to the main content. In addition to this article, you can see a few more articles related to the time function below:

#first. How to convert 24h format to 12h using TEXT . function

+ Step 1: First, you open the Excel file that you need to process.

+ Step 2: To convert the time format from 24h to 12h, click on the cell where you need to output the value (result) => and enter the function formula as follows => and Enter to execute:

=TEXT(choose time value,”hh:mm:ss AM/PM“)

In there:

• hh:mm:ss: Is the value of hours, minutes, and seconds respectively.
• AM/PM: Is the time format AM morning, afternoon PM.

Applying to the example, we get the function formula: =TEXT(A3,“hh:mm:ss AM/PM”)

+ Step 3: After pressing Enter, we will get the result as shown below. Now do Fill cell formula down the list to apply to the entire list.

After doing this, we will get the 12h time format result.

#2. How to convert 12h format to 24h using TEXT . function

With the opposite case if you need convert time from 12h to 24h format Then you apply the following function formula:

=TEXT(time value,“hh:mm:ss”)

Applying to the example, we get the following function formula: =TEXT(B3,“hh:mm:ss”)

Then you Fill the result of the formula cell down to the remaining cells and you’re done.

The end result, after we are done, will look like the image below.

#3. How to calculate total time hours, minutes, seconds using TEXT . function

To calculate the time from 0:00:00 to the current time, enter the following function formula => and press Enter for lookup.:

=TEXT(time value,“hh”)

Applying to the example we get the following function: =TEXT(C3,“hh”)

****************************************

Similarly, with the way to calculate the total number of minutes, you also enter the following function:

=TEXT(time value, “[mm]”)

Note: To calculate the total number of minutes you add square brackets to the value mm.

Applying to the example, we get the function formula: =TEXT(C3,”[mm]”)

****************************************

And with the calculation of the total number of seconds, you also apply the same as calculating the total number of minutes, you use the following function formula:

=TEXT(time value, “[ss]”)

Applying to the example we get the formula: =TEXT(C3,”[ss]”)

Finally, highlight 3 formula values ​​for hours, minutes, and seconds => then hold the plus sign to Fill down the entire list.

And we get the final result as shown below.

#4. Epilogue

Okay, so I just gave you a very detailed guide on how use TEXT function to calculate total hours, minutes, seconds and change format 24h to 12h in Exceland vice versa.

In addition, with the TEXT function, you can also calculate indices such as day, current date, percentage, fraction, or longitude, latitude and currency …

At this point, my article would also like to pause. Hope this trick will be useful to you.

Good luck !

CTV: Luong Trung – techtipsnreview