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 certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories