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...

 

[jtakw]

Hi,

If the Date strings are Real Dates (meaning numbers for Excel), just changing the Cell formatting to Date or dd/mm/yyyy should work, unless it's actually TEXT, then you can try and see if DATEVALUE would help, see following samples:

Excel 2010
	A	B	C	D	E	F	G	H
1		03/30/2012 12:00:00 AM		3/30/2012		3/30/2012 12:00:00 AM		3/30/2012
2		04/02/2012 12:00:00 AM		4/2/2012		4/2/2012 12:00:00 AM		4/2/2012
3
4	Format	dd/mm/yyyy hh:mm:ss AM/PM		Date		TEXT		Date
Sheet21

Cell Formulas
Range	Formula
H1	=DATEVALUE(F1)

Column B are Real Dates, Column F is TEXT.

 

[ggirf14]

Source file is CSV and yes they are formated as text,
with formula =DATEVALUE(F1) I get an error message #Value ! on rows left aligned and right aligned.
Looking at data with notepad I see the following with double quotes and coma: ,"3/16/2012 12:00:00 AM",
I cannot see those caracters when opening with Excel.

 

[jtakw]

I don't deal with CSV files, so don't come in contact with "Hidden Characters" much, but see if this helps:

Excel 2010
	A	J	K	L
1		,"3/16/2012 12:00:00 AM",		3/16/2012
2
3
4	Format	Your Source Data		Date
Sheet21

Cell Formulas
Range	Formula
L1	=SUBSTITUTE(SUBSTITUTE(J1,",",""),CHAR(34),"")+0

 

[ggirf14]

Tried =DATEVALUE(SUBSTITUTE(SUBSTITUTE(O185," ",""), """", "")) with same result

 

[ggirf14]

Same result with =DATEVALUE(SUBSTITUTE(SUBSTITUTE(O185,",",""),CHAR(34),"")+0)

 

[ggirf14]

Leaving for the weekend I will follow up Monday.
Thank you for helping.

 

[jtakw]

Same result with =DATEVALUE(SUBSTITUTE(SUBSTITUTE(O185,",",""),CHAR(34),"")+0)

Try taking OUT Datevalue, use only:

=SUBSTITUTE(SUBSTITUTE(O185,",",""),CHAR(34),"")+0

 

[ggirf14]

Still same issue with
=SUBSTITUTE(SUBSTITUTE(O185,",",""),CHAR(34),"")+0

 

[steve the fish]

What date format does the excel version use? eg DD/MM/YYYY