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

SachinC

New Member
Joined
Sep 25, 2017
Messages
17
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.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try this, and format the cell with the formula as ddd dd/mm/yyyy

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

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,565
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Mon</td><td style="text-align: right;;">30-12-17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Mon 12/30/2017</td><td style="text-align: right;;">Sat Dec 30, 2017</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">9e</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=(<font color="Blue">MID(<font color="Red">A2,8,2</font>)&"/"&MID(<font color="Red">A2,5,2</font>)&"/"&RIGHT(<font color="Red">A2,4</font>)</font>)+0</td></tr></tbody></table></td></tr></table><br />

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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),"/",""))
 
Last edited:

SachinC

New Member
Joined
Sep 25, 2017
Messages
17

ADVERTISEMENT

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?)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,565
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,736
Messages
5,597,815
Members
414,178
Latest member
Octavian Manoli

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
Top