1

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.

2
  • You can create a sub that formats the selected cells with that format and tie it to a keyboard shortcut. – Scott Craner Oct 5 '18 at 22:26
  • Yes, but I don't want to have to do code--my main thing is that I was thinking there was some sort of already existing "quick-button" that would, for example use the locale setting's or something. I know there are so many different date formats--but Windows has the Locale settings and I was hoping there would be a button or auto-format that would simply use one's locale's settings to do a date+time. – jimo3 Oct 6 '18 at 2:48
1
  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

-1

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.

Note:

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

1
  • Write your concern, why down voted, help me to improve !! – Rajesh S Mar 9 '19 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.