Enter military time in Excel

Posted on:  12/19/2016
Enter military time in Excel

Entering Military Time into Excel without having to enter the colon for each entry

If you want to enter military time in Excel, such as 1300 for 1:00 pm or 1730 for 5:30 pm, Excel won’t do by default, but there are several ways to do make it happen. The purpose of this post is to allow you to enter time in quickly by typing just three or four numbers. No need for a colon or am or pm. So when you type 0730 Excel will return 7:30 am.

Type military time and convert to time

  1. Start Excel
  2. Copy and paste this code Excel_VBA_Code_Military_Time_Chris_Menard
  3. Right-click the current worksheet name in Excel.
  4. Click View Code
  5. Paste the code from step 2.
  6. Click File / Close and Return to Excel which is located in the top right.
  7. Type in cell A1 1330 and press Enter. Widen column A if necessary. Type in cell A2 725 and press Enter.

Note: if you want to type in the times in column C, change the 2nd line of the code to “C:C” where it reads “A:A”. It is that simple. See screenshot below code. If you get #######, just widen your column.

Time to excel

Excel – enter military time – YouTube Video

If you want to type in four digits and have Excel change to normal time, just insert a little VBA code on the worksheet tab. Now when you type in 1330, Excel will change it to 1:30 PM. The advantage of this is it saves a lot of time with data entry.

Excel – calculate the difference in time

YouTube video below shows how to calculate the difference in time from two cells with time in them. Example: 4:25 pm – 1:18 pm. If you go over midnight, you will end up with a lot of pound symbols. Widening the column won’t help with the pound symbols. You have to write an IF Statement which is also shown in the video.

Chris Menard

Chris Menard is a Senior Training Specialist at SurePoint Technologies. Chris is certified in Excel, Word, PowerPoint, and Outlook. Menard has a YouTube channel with other 600 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 7 million viewers have very appreciated. Because of Chris's certification and expertise with Microsoft, Chris is a proud member of Microsoft's Creator Team. Being a member of Microsoft's Creator Teams means many of his videos are available on Microsoft 365 YouTube channel and Microsoft support websites.

Categories