Convert raw input from yyyy/mm/dd to dd/mm/yyyy, not just format

[alexcon]

Hi, I really struggle with manipulating date formats and date input fields sometimes.


I've got dates entered as yyyy/mm/dd on a few hundred rows. It's easy to change the format to dd/mm/yyyy but the problem is that the raw input is still yyyy/mm/dd. So it appears excel is getting confused by this backwards date inputting and formatting.

How can swap yyyy/mm/dd to dd/mm/yyyy in both the format and input field?

Any suggestions appreciated.

 

[Nishant94]

Are you looking for something like this:

Unknown
	A	B
1	YYYY/MM/DD	DD/MM/YYYY
2	2018/02/03	03/02/2018
Sheet18

Cell Formulas
Range	Formula
B2	=DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))

 

[Dave Patton]

If the yyyy/mm/dd is Text with no extra characters, try coercing them to dates and then format the true date to your preference say dd-mmm-yy.
To coerce, try the following:
copy a blank cell Ctrl+C
select the relevant range and Paste Special | Add

 

[alexcon]

Are you looking for something like this:

Unknown

	A	B
YYYY/MM/DD	DD/MM/YYYY
2018/02/03

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]03/02/2018[/TD]

</tbody>

Sheet18​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Tried this, I just get #VALUE! as the result

 

[Rick Rothstein]

Hi, I really struggle with manipulating date formats and date input fields sometimes.


I've got dates entered as yyyy/mm/dd on a few hundred rows. It's easy to change the format to dd/mm/yyyy but the problem is that the raw input is still yyyy/mm/dd. So it appears excel is getting confused by this backwards date inputting and formatting.

How can swap yyyy/mm/dd to dd/mm/yyyy in both the format and input field?

You should be able to change them into dates directly within their existing cells. Select the entire column containing those backward dates, then change the cells' format to "General". Next, call up the "Text to Columns" dialog box ("Data" tab, "Data Tools" panel, "Text To Columns" button) and click the "Finish" button as soon as the dialog box appears. I think that should make your backward dates into real Excel dates.

 

[alexcon]

There was an underlying issue with my win10 settings. I had to change the date time format there first for it work.