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: