Date-time format as you like...

[Mark Green]

When I look into Date formats offered I don't see the one I like:

Nov 30, 2016 10:59 AM

Is it possible to format a column so that the date-times will show as above?

TIA

 

[Shiremaid]

You can set a custom format (bottom of the list on the left when you open the formatting dialog box)

There will be a place to type your own format

I think typing this
mmm dd, yyyy h:mm AM/PM
should give the format you want.

 

[Mark Green]

You can set a custom format (bottom of the list on the left when you open the formatting dialog box)

There will be a place to type your own format

I think typing this
mmm dd, yyyy h:mm AM/PM
should give the format you want.

Thanks very much that worked out nicely.

 

[Mark Green]

You can set a custom format (bottom of the list on the left when you open the formatting dialog box)

There will be a place to type your own format

I think typing this
mmm dd, yyyy h:mm AM/PM
should give the format you want.

This might be a little trickier.
I would like to have the 'AM/PM' as 'am/pm' (lower case).
I tried entering it but Excel put it back as upper case.

 

[Anand Sharma]

HI,

you can use text function to format date as desired

=TEXT(X7, "mmm/dd/yyyy hh:mm am/pm")

 

[Tetra201]

This might be a little trickier.
I would like to have the 'AM/PM' as 'am/pm' (lower case).
I tried entering it but Excel put it back as upper case.

Try using this custom format: mmm d, yyyy h:mm a/p\m

 

[Anand Sharma]

HI,

you can use text function to format date as desired

=TEXT(X7, "mmm/dd/yyyy hh:mm am/pm")

=MID((TEXT(X7, "mmm/dd/yyyy hh:mm am/pm")),1,LEN((TEXT(X7, "mmm/dd/yyyy hh:mm am/pm")))-3)&" "&LOWER(RIGHT(TEXT(X7, "mmm/dd/yyyy hh:mm am/pm"),2)) for lowering am/pm

 

[Mark Green]

=MID((TEXT(X7, "mmm/dd/yyyy hh:mm am/pm")),1,LEN((TEXT(X7, "mmm/dd/yyyy hh:mm am/pm")))-3)&" "&LOWER(RIGHT(TEXT(X7, "mmm/dd/yyyy hh:mm am/pm"),2)) for lowering am/pm

Thanks Anand. I got it to work, in so far as I can put the formula in a cell and have it return the correct date format in that cell.
Now the next challenge for me is to be able to use VBA to copy that formula into a cell. Not having luck so far.

After I can do that, the game plan is to copy down the formula (temporarily) as many rows as the dates are.
Then copy/paste special (values) back into the original date cells.
Then clear out the temp date formulae.

I'm thinking there must be an easier way just to change from AM to am?

 

[tygrrboi]

Tetra's suggestion in post #6 works perfectly for me...

 

[Mark Green]

Tetra's suggestion in post #6 works perfectly for me...

I tried to implement it yesterday but didn't have any luck.
I didn't know how to use it.

But just now I went back to it and figured out what to do with it.
It works perfectly!

So I would like to thank both you and Tetra for your
help!
I really appreciate it.