Enter time in Excel without a colon

At first I thought this would be a simple thing to configure Excel to allow me to input military time in a spreadsheet without having to type the colon. Nope!

I created an Excel spreadsheet to track my work hours and calculate total hours in a day minus my lunch break. That was easy enough but as I entered the times I quickly realized that typing the colon was slow and tedious. “:” is a very inconvenient keystroke when it comes to typing numbers.

I searched the Internet for a solution and quickly realized that this seemingly easy auto-format issue was complicated by the fact that it involves calculation of time. If the cells contained simple text, it would be have been easy. I searched for “Excel automatically add a colon in military time.” I found a few solutions but none of them were ideal. They required “helper cells” adjacent to the cells used for input or weren’t taking into consideration that the cells contained time values that required calculations. Eventually, I found a reference to a VBA macro solution by Chip Pearson.

Now, how do I implement it? I know almost nothing about macros and I am not a programmer. I found an easy way to open the VBA editor in Excel – hold down Alt and press F11. This opens a window where you can paste in the code from Chip Pearson. The code for the macro begins below his code for entering dates without typing “/” and just after his “rules for conversion” examples.

The macro didn’t seem to do anything. Hmmm, I must be missing something simple. Yep! You must “save” the macro. So simple, try to find that in any directions.

It works like a charm. Thank you, Chip! The only thing you must do that he does not mention is format the cells as time.