Excel, how to change what times default to AM/PM. Willing to use macros

by Andrew Raymond Peters   Last Updated July 12, 2019 14:01 PM - source

In all time formats entering “12:” will return “12:00 PM” for noon. If I wanted midnight I could enter “12:00 a” which is 4 more keystrokes and is ok because I never have a need to track times at night. However this common sense breaks when I enter “1:” and return “1:00 AM” which is not ok for the same reason.

I’m aware I could use military time or simply change the A to a P. That is my current solution but I see it as a band aid. My intent is to learn how to change what times default to AM or PM passively.

1- is there a deceptively easy way to tweak the defaults in settings?

2- is there a VBA/macro solution to this? Again I’m looking for passive change. I don’t want to create a hot key to change the second to last character in a string in a given cell to “P”. I want that to happen as soon as I hit enter.

3- this one is purely philosophical and would have to be answered by Microsoft employee but somebody at Microsoft had to have suggested that 12 default to noon unlike every other hour. Why didn’t they extend this idea to other reasonably afternoon times like noon to 5 or 6?

Related Questions

No Startup folder in Microsoft Office for Mac 2011

Updated April 07, 2017 08:01 AM