Converting a text date to numnerical date

[Flashboy]

Hi guys

I have data that come in the following format 12-Oct-2017, how can I change this date to become 12/10/2017

Thanks

Flash
[TABLE="width: 179"]
<colgroup><col width="239" style="width: 179pt; mso-width-source: userset; mso-width-alt: 8740;"> <tbody>[TR]
[TD="class: xl64, width: 239, bgcolor: #C6EFCE"]

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

 

[63falcondude]

Re: Help needed Converting a text date to numnerical date

You have a couple of options here.

What I would do in this scenario, is highlight the column with the text dates > Data > Text to Columns > Next > Next > Date: DMY > Finish.

Then you can format it however you'd like.

 

[wideboydixon]

Re: Help needed Converting a text date to numnerical date

It depends if it's recognised as a date or whether it's text. Three options:

1. If the cell is a recognised date, highlight the cell, press Ctrl+1 and select a Custom Number format - use "dd/mm/yyyy"
2. If the cell is recognised as text, use a formula e.g.:

=DATEVALUE(A1)

You can then use Ctrl+1 on the new cell to set the right date format.
3. If you want the output as text (and not a date) then you could use:

=TEXT(DATEVALUE(A1),"dd/mm/yyyy")

WBD

 

[Dave Patton]

Re: Help needed Converting a text date to numnerical date

Excel 2010
	A	B	C	D	E	F	G
1	12-Oct-2017	12/10/17	If an actual date, custom format to your preference B1
2	12-Oct-2017	12/10/17	Add 0 and custom format.
3
1b

Cell Formulas
Range	Formula
B1	=A1
B2	=A2+0

Add 0 to the text to convert to a number and custom format. N.B. I recommend for clarity 12-Oct-17 or 12-Oct-2017
Custom format dd/mm/yy