Separate Date

[Reec0n]

An easy one for the forum I'm sure:

I have a list of these:

[TABLE="width: 164"]
<tbody>[TR]
[TD="class: xl65, width: 164"]Wed Aug 01 11:13:36 2018

I would like to take the date only and have them in a separate column like this 01/08/2018

All help welcomed,

tia [/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

Assuming it's only the date and time in your cell:

=(MID(A1,5,6)&", "&RIGHT(A1,4))+0

Format as the date format dd/mm/yyyy

Excel 2010
	A	B
1	Wed Aug 01 11:13:36 2018	01/08/2018
Sheet1

Cell Formulas
Range	Formula
B1	=(MID(A1,5,6)&", "&RIGHT(A1,4))+0

 

[Reec0n]

Assuming it's only the date and time in your cell:

=(MID(A1,5,6)&", "&RIGHT(A1,4))+0

Format as the date format dd/mm/yyyy

Excel 2010

	A	B
Wed Aug 01 11:13:36 2018

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]01/08/2018[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=(MID(A1,5,6)&", "&RIGHT(A1,4))+0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for this; however, I get a #VALUE ! error? it is the same as above so not sure -if i remove the +0 at the end i get date as Aug 01, 2018 :|

 

[Scott Huish]

I wonder if you have extra spaces in that.

Does this work for you?

=(TRIM(MID(A1,5,6)&", "&RIGHT(A1,4)))+0

 

[Reec0n]

I wonder if you have extra spaces in that.

Does this work for you?

=(TRIM(MID(A1,5,6)&", "&RIGHT(A1,4)))+0

Hi,

No extra spaces and still not joy

 

[Scott Huish]

Can you post an exact example of the data where it is not working?