Date format from mm/dd/yyyy hh:mm:ss AM/PM to dd/mm/yyyy

[ggirf14]

I cannot find an exact answer to handle the following dates:
3/30/2012 12:00:00 AM
04/02/2012 12:00:00 AM

Using Excel 2016 I tried
1- Formatting with custom dates including [$-409]mm/dd/yyyy hh:mm:ss AM/PM;@
2- Various versions of LEFT(TRIM(RIGHT(SUBSTITUTE...

 

[ggirf14]

Reading from csv, I have tried all date format I can think of in an xlsx file with Excel 2013.

 

[jtakw]

Good morning, I can't see the image in Post #9 , you need to upload it to a file sharing site and include the link in your post.

But let's try this, =CLEAN(A1)

where A1 is your data, what do you get afterwards? The CLEAN function removes All non-printable characters from imported data.

 

[ggirf14]

Hope this link to image will work

https://drive.google.com/file/d/1BvObRX1rROlaIJprQSyX6-3jE4gt9Fe6/view?usp=sharing
link

Clean

does not help.

 

[steve the fish]

Looks to me like your machine uses DD/MM/YYYY date notation. This may work:

=IF(ISNUMBER(O1),DATE(YEAR(O1),DAY(O1),MONTH(O1)),INT(SUBSTITUTE(MID(O1,FIND("/",O1)+1,99),"/","/"&LEFT(O1,FIND("/",O1)))))

 

[Dave Patton]

Did you confirm that your system is set to Regional Settings of dd-mm-yy?

Did you try Data | Text to Columns | date MDY ?

Excel 2010
	A	B	C
2			Data Text to Columns MDY
3	04/02/2012 12:00:00 AM		Apr 02, 2012
4	3/30/2012 12:00:00 AM		Mar 30, 2012
5	04/02/2012	Apr 02, 2012	Apr 02, 2012
6	3/30/2012	Mar 30, 2012	Mar 30, 2012
7	12/30/2017	Dec 30, 2017	Dec 30, 2017
8	1/30/2017	Jan 30, 2017	Jan 30, 2017
9	1/3/2017	Jan 03, 2017	Jan 03, 2017
10	5/8/2017	May 08, 2017	May 08, 2017
11	10/20/2017	Oct 20, 2017	Oct 20, 2017
12
4b

Cell Formulas
Range	Formula
B5	=DATE(RIGHT(A5,4),LEFT(SUBSTITUTE(A5,"/"," "),2),SUBSTITUTE(LEFT(RIGHT(A5,7),2),"/",""))

 

[ggirf14]

Changing Regional Settings is not a setting I can consider to change in my work environment but I had a peak.
Strange setting Short date is set to dd/MM/yyyy
Long date are set to MMMM-dd-yy
Not sure of the impact having different order in short and long date format. That might have been why it was so tough to get Excel to understand the dates. New job, new computer settings ...

For the moment a big thank you to everyone for their efforts.
Steve the fish post # 14 is doing the job https://drive.google.com/open?id=1akfTxzll29UzQUJbDhgqMYTtzZQIDs0_
Dave's last post #15 is not working for me.
Difinitely looks like regional settings date format may be the real root cause, a bit scary to learn Excel is not handeling the same with different regional setting.
I would like to understand more as I try to make my work as portable as possible.



Gilles Girard.