• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how do I format a date as Wed, Nov 27, 2019 12:00 p.m.

P

pflickner

New Member
We have an application that accepts the date only in this format, but the only thing I can see to get the dow is the full Wednesday. We also want to add 8 hours to whatever the value is as the time, for some reason, is +1 GMT. We're stuck, Thanks for any help.
 
It would help if you upload sample.

Let's say that you have...
11/27/2019 12:00
In A1.

Using formula...
In B1:
=SUBSTITUTE(SUBSTITUTE(TEXT(E10+8/24,"ddd, mmm dd,yyyy h:mm AM/PM"),"AM","a.m."),"PM","p.m.")

This will give you...
Wed, Nov 27,2019 8:00 p.m.
 
I don't have a sample yet - we're trying to automate something that we've had to do manually for forever. I really appreciate the fast response. And this is absolutely perfect. I just have to use military time for it to work properly. :) Thanks!
 
Last edited:
You will need to replace the NOW() in this with your date and time:
=TEXT((NOW()+TIME(8,0,0)),"dddd, mmm dd, yyyy hh:mm am/pm")

If your timestamp is in cell C7, then:
=TEXT((C7+TIME(8,0,0)),"dddd, mmm dd, yyyy hh:mm am/pm")
 
Oh, if you don't need it in a.m./p.m. format, you can drop substitute functions ;)
Since you had it in your title I assumed it was part of requirement. am/pm or AM/PM is part of custom format set, but a.m./p.m. isn't.
 
You must log in or register to reply here.
Back
Top