Is there a quick way to do a common date-time format in Excel?

Posted on


Can someone please help me find, built into Excel (any version) a ready-made format for Date/Time like:
MM/dd/yyyy hh:mm AM/PM

I can’t imagine this is not a common format? 10/05/2018 05:03 PM
I like the zero-padded single-digits, and the 4-digit year, yet nowhere in the “Date” format list or the “Custom” format list does anything close exist.

Every time I need to format a date/time column in Excel I need to hand type that format string in.

EDIT: Based on the responses, which I do appreciate–I need to clarify the question. I know how to select ranges and choose formats, etc. I was just bewildered that of all the formats available in Excel’s list in the Format Cells dialog–none of them were what I would consider a very common Date/Time format (if not the most common in the US–and the default locale format in the US). I thought I may have been digging too deep and that there was some hot-key for “Default Locale DateTime” format.
Thanks for any help on this.


  1. Select the cell(s) you want to apply the formatting to.
  2. Select the home ribbon
  3. In the number section select the expand button in the bottom right corner or from the drop down menu select More Number Formats…

example 1

  1. In the Format Cells wizard, select the Number Tab.
  2. In the Category: area select Custom.

Example 2

  1. Between the sample box and the scroll area listing all the formats where it shows General in the picture above, enter the following format:

    mm/dd/yyyy hh:mm AM/PM

  2. Use Format painter on the home ribbon to apply the custom format to other cells

In my custom formats, when I scroll down there is a custom date time that does come close:

yy/mm/dd hh:mm

It does pad the zeros, but does not give AM/PM. Also the order of dates is mixed up from what you want, but it does match my system settings.

Example 3

The Fastest method I can suggest you is,, single line VBA code.

Thisworkbook.worksheets("Sheet1").range("A1:A150").numberformat = "MM/DD/YYYY HH:MM AM/PM"

How it works:

  • Press Alt+F11 to open VB editor.
  • Form the View menu find & Click Immediate Window to open.
  • Copy & Paste this Code in Immediate Window & finish with Enter button.
  • Excel will format the said Data Range(A1:A150) for the Specified Date & Time.


Adjust Sheet Name & Cell references in the code as needed.

Leave a Reply

Your email address will not be published. Required fields are marked *