Macro to set date format in specific columns

[IKHAN]

Hi, Need help with setting up custom date format "ddd mmm dd, yyyy - hh:mm AM/PM" in specific columns K and L.

If "Fri 4/29/2016 3:16 PM" copied onto cell ,Should change to
"Fri Apr 29, 2016 - 03:16 PM" format.

 

[Hui]

Select Column L
Ctrl+1
Select Custom
In the Number Format dialog enter: ddd mmm dd, yyyy - hh:mm AM/PM
as data is entered it will use this format

Otherwise if you are copying in data
Use the Format Painter Tool to copy the format of an existing cell onto the new data

 

[IKHAN]

Date is copied from another application and pasted into excel.Column K an L has been formatted to "ddd mmm dd, yyyy - hh:mm AM/PM" and format painter doesn't work.

When date "Fri 4/29/2016 3:16 PM" is copied , It doesn't change to "Fri Apr 29, 2016 - 03:16 PM"

Require a macro to change to format above when cell is copied and pasted .

 

[Hui]

It sounds like it is being pasted as Text
Have you tried using the Data, Text to Columns function?

Can you please supply a sample file with a dozen lines in it ?

 

[IKHAN]

Uploaded test file..Not sure how to use data,text columns..staff has to enter dates as email comes in daily