Skip to main content

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

Author: Xiaoyang Last Modified: 2020-11-05
doc convert 12 hour to 24 hour 1

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

Convert time format between 12 hour and 24 hour with formulas


arrow blue right bubble Convert time format between 12 hour and 24 hour with formulas

The following formulas can help you to convert the 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 a blank cell where you want to output the result, and then drag the fill handle down to the cells that you want to apply this formula, and all the time format has been converted from 12 hour to 24 hour, see screenshot:

doc convert 12 hour to 24 hour 2

Convert time format from 24 hour to 12 hour:

To convert the time format from 24 hour to 12 hour, please type this formula: =TEXT(A2,"hh:mm:ss AM/PM") into a blank cell to locate the result, and drag the fill handle down to the cells that you want to contain this formula, and all the 24 hour time format cells have been converted to 12 hour format, see screenshot:

doc convert 12 hour to 24 hour 3

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (7)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
the formula does not work in my pc, what seems to be the problem?
This comment was minimized by the moderator on the site
70500 PM
70800 PM
70900 PM
70800 PM
83400 PM
83700 PM
83500 PM i want change timings 12hrs to 24 hrs but i can't able to change above mentioned timings kindly help me out on this
This comment was minimized by the moderator on the site
I want to add one general number and time. Eg. 12.45(general number) in one cell and 1.30(time) in another cell . when I add both the result showing 13.75. but I want the result in time as 14.15. is it possible?
This comment was minimized by the moderator on the site
use : not . to recognize as time format
if u use . it recognize as decimal number
This comment was minimized by the moderator on the site
Hi, Vinesh,
Sorry, this can not be solved in Excel worksheet.
Thank you!
This comment was minimized by the moderator on the site
When the formula is entered, it gives a circular reference warning and even after that 0400 shows as 400.
This comment was minimized by the moderator on the site
thanks for share this formula.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations