Time function, 24 hour format to 12 o'clock or vice versa on Excel - Knowledge sharing blog

Note: Please read the article carefully before proceeding! If in the process of using you encounter any errors, such as broken download links, slow loading blog, or unable to access a certain page on the blog ... then please inform me here. Thanks!

In today's Excel section, I will share with you how to calculate the total time in hours, minutes, seconds and change the 24-hour time format to 12 o'clock and vice versa using the TEXT function.

Okay, now we will get to the main content. In addition to this article, you can see a few more articles related to time functions below:

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

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

excel-time-in-excel (1)

+ 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 formula as follows => and Enter to execute:

= TEXT(Choose a time value, ”hh: mm: ss AM / PM")

Inside:

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

Applied to the example we get the formula: = TEXT (A3, "hh: mm: ss AM / PM")

excel-time-in-excel (2)

+ Step 3: After pressing Enter we will get the results as shown below. Now, you should fill the formula cell down the list to apply to the whole list.

excel-time-in-excel (3)

Once done, we will have the results format time 12h.

excel-time-in-excel (4)

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

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

= TEXT(worth the time,"Hh: mm: ss")

Applied to the example we have the following function formula: = TEXT (B3, "hh: mm: ss")

excel-time-in-excel (5)

Then you Fill the formula cell results down to the remaining cells is done.

excel-time-in-excel (6)

The final result, after we are done, will be as shown below.

excel-time-in-excel (7)

# 3. How to calculate total time hours, minutes, and seconds with the TEXT function

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

= TEXT(worth the time,"Hh")

Applied to the example we have the following function: = TEXT (C3, "hh")

excel-time-in-excel (8)

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

Similarly, with the total number of minutes you enter the following function:

= TEXT(worth the time, "(Mm)")

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

Applied to the example we have the function formula: = TEXT (C3, ”(mm)”)

excel-time-in-excel (9)

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

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

= TEXT(worth the time, "(Ss)")

Applied to the example we have the formula: = TEXT (C3, ”(ss)”)

excel-time-in-excel (10)

Finally, highlight the three values ​​of the hour, minute, and second formulas => and hold the plus sign to fill the whole list.

excel-time-in-excel (11)

And we get the final result as shown below.

excel-time-in-excel (12)

# 4. Epilogue

Okay, so I just gave you very detailed instructions Use the TEXT function to calculate the total number of hours, minutes, and seconds and Change 24h format to 12h in Excel, and vice versa.

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

At this point, my article would like to pause. Hope this tip will be helpful to you.

Good luck !

CTV: Luong Trung - Blogchiasekienthuc.com

Note: Was this article helpful to you? Do not forget to rate the article, like and share it with your friends and relatives!