Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Custom Date Format Question in Excel

Copper Contributor

Hi, 

 

I'm hoping someone can help me with this. 

 

I am trying to create a custom date format that would look like "SEPTEMBER 26 @ 11:59 PM". 

By typing in the following formula "[$-en-US]mmmm d h:mm AM/PM;@" I was able to get "September 5 12:00 AM".

When I made the format the time was 8:58 PM not 12:00 AM, so if anyone knows how to insert the @, get the time right, and make it all caps, I would really appreciate it. 

 

= Andrea

 

3 Replies

@nurkoandrea Try below custom number format.

[$-en-US]mmmm d \@ h:mm AM/PM

Harun24HR_0-1662430469231.png

Or TEXT() function like

=TEXT(A1,"[$-en-US]mmmm d \@ h:mm AM/PM")

Harun24HR_1-1662430513092.png

 

That worked! Thank you very much, but do you also know how to make it all caps?
best response confirmed by JimGMac (MVP)
Solution
If it helps then please mark it as solved (tick mark the answer). In case of formula you can try =UPPER(TEXT(A1,"[$-en-US]mmmm d \@ h:mm AM/PM")). I am not sure about custom formatting to make it caps.
1 best response

Accepted Solutions
best response confirmed by JimGMac (MVP)
Solution
If it helps then please mark it as solved (tick mark the answer). In case of formula you can try =UPPER(TEXT(A1,"[$-en-US]mmmm d \@ h:mm AM/PM")). I am not sure about custom formatting to make it caps.

View solution in original post