Convert long text date in CSV to proper Excel date

[rstuart]

Currently exporting date into Excel in this format: Jun 22, 2019, 09:38 AM

Update: the dates appear in each cell within the column (1 date per cell in a column needs to be converted into another column of short dates)

Want to convert to: 22/06/2019

Have tried long way of Text to Columns and replacing "Jun" and other months to "6" so I can use DATE function to put it together to get result - but would like a quicker method with having to insert all the columns to unpack the date just to put it back together.

Thanks

 

[Peter_SSs]

Jun 22, 2019, 09:38 AM

Is that in one cell, 3 cells or something else?

 

[rstuart]

Sorry yes - 1 date per cell down the column

 

[Peter_SSs]

Sorry yes - 1 date per cell down the column

Does this work for you?
If not, a few more examples and expected results would help as one isn't much use in trying to find any patterns (eg whether the 'day' part always has 2 digits or not).

<b>Date</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:164px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Jun 22, 2019, 09:38 AM</td><td style="font-size:10pt; text-align:right; ">22/06/2019</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=DATEVALUE(MID<span style=' color:008000; '>(A1,FIND<span style=' color:#0000ff; '>(",",A1)</span>-2,2)</span>&LEFT<span style=' color:008000; '>(A1,3)</span>&MID<span style=' color:008000; '>(A1,9,4)</span>)</td></tr></table></td></tr></table>

 

[Rick Rothstein]

Your date format is "backward" from the one I use, so I have no way of testing whether this formula will work for you or not (it works correctly for me in my locale)... give it a try and let me know

=INT(SUBSTITUTE(A1,",","",2))

Note: You will have to format this cell with your date format, what you need to check is if the day and month numbers are correct.

 

[Rick Rothstein]

Your date format is "backward" from the one I use, so I have no way of testing whether this formula will work for you or not (it works correctly for me in my locale)... give it a try and let me know

=INT(SUBSTITUTE(A1,",","",2))

Note: You will have to format this cell with your date format, what you need to check is if the day and month numbers are correct.

If the above does not work for you, then perhaps this one will...

=0+MID(SUBSTITUTE(A1,","," "&LEFT(A1,3)),5,11)

 

[Peter_SSs]

.. then perhaps this one will...

=0+MID(SUBSTITUTE(A1,","," "&LEFT(A1,3)),5,11)

This one for me, not the other.
Different date systems make it awkward, don't they?