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.
- Select the cell(s) you want to apply the formatting to.
- Select the home ribbon
- In the number section select the expand button in the bottom right corner or from the drop down menu select More Number Formats…
- In the Format Cells wizard, select the Number Tab.
- In the Category: area select Custom.
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
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:
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.
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:
Alt+F11to 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.
Sheet Name &
Cell references in the code as needed.