Convert ddd mm/dd/yyyy to ddd dd/mm/yyyy

[SachinC]

Hi,
I have an issue.
I have some data in Excel which I need to covert.
The data is in the format: Mon 12/30/2017 where it needs to be Mon 30/12/2017.
I have tried Data>Text To Columns>DMY with no avail - so please do not mention that.
Helpful advise welcomed as it's bugging me!
Thank you.

 

[Jonmo1]

Is the value in the cell actually a DATE formatted as ddd mm/dd/yyyy
Or is it a TEXT string ?

What does this return
=ISNUMBER(A1)
Where A1 is a cell with the value in it.

 

[SachinC]

Hi there,
FALSE
Thanks.

 

[Jonmo1]

Try this, and format the cell with the formula as ddd dd/mm/yyyy

=REPLACE(A1,1,4,"")+0

 

[Dave Patton]

Excel 2010
	A	B
1	Mon	30-12-17
2	Mon 12/30/2017	Sat Dec 30, 2017
9e

Cell Formulas
Range	Formula
B2	=(MID(A2,8,2)&"/"&MID(A2,5,2)&"/"&RIGHT(A2,4))+0

Data Text to Column will work. First separate the "Mon " part.
The formula works if the text has two characters for day and month.
N.B. There are no trailing spaces.

 

[Jonmo1]

The formula I posted will only work if you have US date format on your pc.
Dave's formula handles that, but assumes you will always have 2 digit day and month.

This will work for US or UK, either single or double digit day/month

=DATE(RIGHT(A1,4),SUBSTITUTE(RIGHT(LEFT(A1,6),2),"/",""),SUBSTITUTE(LEFT(RIGHT(A1,7),2),"/",""))

 

[SachinC]

Try this, and format the cell with the formula as ddd dd/mm/yyyy

=REPLACE(A1,1,4,"")+0

SC - Hi, this worked on some - but didn't work with this one for some reason: Fri 10/20/17 (one example of many - please help?)

 

[Jonmo1]

SC - Hi, this worked on some - but didn't work with this one for some reason:

Probably because you're not in US date format.

See post # 5 and/or 6

 

[Dave Patton]

Hello SachinC

Try the suggestion in post 5.


What regional settings exist on your computer?



If your data is consistent, a formula solution is possible.

Post a few rows of your data and the expected result.