Converting dates to standard format

[richardthelionheart]

10.05.18

19.04.18

I'm importing dates that look like this. I'm trying to get - 26/05/18 or 26/5/18 or 26/05/2018 or 26/5/2018 Any of these will do as they are all read as dates which I can them convert to the microsoft number for that date. I do not know what format was used in the original "dates" I would appreciate any help as I have tried virtually everything I know for 4 hours trying to solve this.

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
[TD="width: 64"]26.05.18[/TD]

</tbody>

 

[Fazza]

what about replacing . with /

So,
select data
CTRL-H
find .
replace /

change all

 

[Dave Patton]

Excel 2010
	A	B
2
3	26.05.18	26-May-18
4	10.05.18	10-May-18
5	19.04.18	19-Apr-18
6
4d

Cell Formulas
Range	Formula
B3	=--SUBSTITUTE(A3,".","-")
B4	=--SUBSTITUTE(A4,".","-")
B5	=--SUBSTITUTE(A5,".","-")

Format to your preference.

 

[richardthelionheart]

Excel 2010

	A	B

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

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]26.05.18[/TD]
[TD="align: right"]26-May-18[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]10.05.18[/TD]
[TD="align: right"]10-May-18[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]19.04.18[/TD]
[TD="align: right"]19-Apr-18[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

4d​

[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] "]B3[/TH]
[TD="align: left"]=--SUBSTITUTE(A3,".","-")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=--SUBSTITUTE(A4,".","-")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=--SUBSTITUTE(A5,".","-")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Format to your preference.

Thanks to both replies. I really appreciate it. Dave's one worked really well.