Table of Contents
Converting between standard time and military time in Excel can be achieved by using the built-in functions and formatting options available in the software. Standard time, also known as 12-hour clock, is the most commonly used time format in everyday life, while military time, also known as 24-hour clock, is often used in the military and other industries. To convert between these two formats in Excel, one can use the “Text to Columns” function to separate the hours and minutes, and then use the “TIME” function to convert the time into the desired format. Additionally, Excel offers formatting options to display time in either standard or military format. By following these steps, one can easily and accurately convert between standard time and military time in Excel.
Excel: Convert Between Standard Time & Military Time
You can use the following formulas to convert between standard time and military time in Excel:
Formula 1: Convert Standard Time to Military Time
=TEXT(A2,"HHMM")
This particular formula converts the standard time in cell A2 to military time.
Formula 2: Convert Military Time to Standard Time
=TIMEVALUE(LEFT(A2,2)&":"&RIGHT(A2,2))
This particular formula converts the military time in cell A2 to standard time.
The following examples show how to use each formula in practice.
Example 1: Convert Standard Time to Military Time
Suppose we have the following list of standard times in Excel:
We can type the following formula into cell B2 to convert the standard times to military times:
=TEXT(A2,"HHMM")
We can then click and drag this formula down to each remaining cell in column B:
Column B now displays each standard time from column A as a military time.
Note: If you’d like to display the seconds in the military time as well, you can use the following formula instead:
=TEXT(A2,"HHMMSS")
The following screenshot shows how to use this formula in practice:
Example 2: Convert Military Time to Standard Time
Suppose we have the following list of military times in Excel:
We can type the following formula into cell B2 to convert the military times to standard times:
=TIMEVALUE(LEFT(A2,2)&":"&RIGHT(A2,2))
We can then click and drag this formula down to each remaining cell in column B:
By default, the times in column B are shown as decimals.
To format these decimals as times, highlight the cell range B2:B10 and then click the Number Format dropdown on the Home tab and then click Time:
Column B will now display each military time from column A as a standard time:
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: