How to convert military time to standard time in Excel?
Maybe you have accepted a worksheet with a list of military time as shown as below, and you are used to viewing standard time, so you want to convert these military time to standard time in Excel, how can you do?
Convert military time to standard with formula in Excel
In Excel, you can convert the military time to standard time with formula quickly.
1. Select a blank cell to type this formula =TIMEVALUE(LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)) (A1 indicates the cell you want to convert the military time to standard time, you can change it as you need), and press Enter key, then drag the fill handle to fill the range you want. See screenshot:
2. Then select the range B1:B6 (the range you use the above formula, you can change it as you need), and right click to click Format Cells. See screenshot:
3. In the Format Cells dialog, click Number tab, and click Time from the Category box, then select the standard time format from the right section. See screenshot:
4. Click OK, you can see the result:
Tip: If the military time just has hour and minute such as 2323, you can use this formula =TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)) and then follow the above steps to convert it to standard time. See screenshot:
Convert standard time to military with formula in Excel
To convert standard time to military time, you also can use a formula to solve it.
Select a blank cell, and type this formula =TEXT(A1,"HHMMSS") (A1 indicates the cell you want to convert the military time to standard time, you can change it as you need), and press Enter, then drag the fill handle to fill the range you want. See screenshot:
Tip: If the standard time just have hour and minute such as 11:23 PM, you can use this formula =TEXT(A1,"HHMM"). See screenshot:
Tip.If you want to convert nonstandard dates to the standard date mm/dd/yyyy , please try to use the Kutools for Excel’s Convert to Date as shown in the following screenshot. It’s full function without limitation in 30 days, please download and have a free trial now.
Quickly convert nonstandard date to standard date formattiing(mm/dd/yyyy)
In some times, you may received a workhseets with multiple nonstandard dates, and to convert all of them to the standard date formatting as mm/dd/yyyy maybe troublesome for you. Here Kutools for Excel's Conver to Date can quickly convert these nonstandard dates to the standard date formatting with one click. Click for free full featured trial in 30 days!
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
Yes, but you can copy and pasted the formula result as value, then delete the original column and formula column if you do not need them any more, Now you will leave only one result column.
Sorry for reply you too late.In my version, the formula can work for three digits number which do not need to be add the leading zero. If it cannot to work for you， you can add leading zero by format the cells as 0000 in the Format Cells > Custom.
What if the military time doesn't contain the zero in front? For example 0714 is showing as 714. How do you format these times quickly so that your formula above works correctly?
Thanks, it seemed to work great even though I am not that sharp with excel. I was able to find a pre-made excel spreadsheet that converts standard time to military time automatically for my needs at another site. You can find it here: http://www.militarytime.us/military-time-converter/ (the link is under the converter tool that is also quite nice for a quick single conversion). Thought others might find it useful as well.
This works well, but there's rounding errors after a few instances of this. How do you fix that? (I. E. I get down to where I'm adding 20 seconds to my start time, but the output time is 3 seconds later than it should be.)
