Converting dates to standard format

richardthelionheart

Board Regular
Joined
Mar 25, 2016
Messages
95
Office Version
  1. 2016
Platform
  1. Windows
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>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Excel 2010
AB
2
326.05.1826-May-18
410.05.1810-May-18
519.04.1819-Apr-18
6
4d
Cell Formulas
RangeFormula
B3=--SUBSTITUTE(A3,".","-")
B4=--SUBSTITUTE(A4,".","-")
B5=--SUBSTITUTE(A5,".","-")


Format to your preference.
 
Upvote 0
Excel 2010
AB

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

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top