Date Formatting Issues when copying for a cvs workbook

[jckeddy]

Does anyone know how to fix a formatting issue when copying from a cvs excel workbook? 

When I try to copy from a cvs workbook to a xlsx workbook on a Mac in Office 365 the date formatting doesn't work.  I have tried "Use Destination Formatting", "Match Destination Formatting", "Values" and Numbers Formatting", and" Formatting Only" with no success. 

The pics below are from the xlsx workbook in Custom Menu.  In pic 1 it shows that D1 the desired format with the correct sample which work perfectly.  While pic 2 shows a different format with the right corresponding sample and works perfectly when applied, which I can replicate in any other cells in the worksheet.  It is only when I copy from the cvs workbook, in cell D2 and any other cells, that I have a formatting issue.   Pics 3&4 show that D2 doesn't format to the desired format and that the sample is incorrect as well in both pics.

[#2]

@jckeddy That's because when you copy from a csv, you are copying text. Excel does not know that that the text string "Saturday, January 11, 2020 ......" is a date. Get rid of "Saturday, " to begin with and tell Excel the "January 11, 2020" is a date based on MDY (= month-day-year). You can use DATA - Text to columns to do this. The pictures below demonstrate the process.

[#3]

@Riny_van_Eekelen  Thanks for the info however,  when I followed your pictures it ended up putting each part of the date and time in separate columns.  See Pic bellow.

[#4]

@jckeddy  Forgot to tell you that I will need the time as well, your final result didn't include that.

[#5]

@jckeddy 

It looks like you did this:

Make it like this (i.e remove the automatic column separators except the one shown below):

Then you should get two columns. Make sure that the date becomes "Date: MDY" and leave the time as "General".

Then it should create two columns in your sheet. To combine Date and time, do like what's in the picture below. Now you have a Date/Time that you can format to your liking.