Excel reformat a cell that looks like a date automaticly when using replace even if it formatted as text beforehand

Put 08/10/2013 23:58 in to a cell that has been formatted as text. Now remove the 20 manually (08/10/13 23:58). Excel does nothing to the cell. Now use find and replace and look for /2013 and replace with /13... Excel will reformat the cell from text to a date.... argh!

FYI the date coming out in my report is in a US format and I'm UK... this is the 10th August 2013 at 23:58....

If I could just switch off this function I would be able to clean my data up using Replace, some clever formulas and reformat it to the UK standard then switch on the cell formatting to Date.

The date format in the column that I have to deal with is as follows....

mm/dd/yy
mm/dd/yyyy
dd/mm/yyyy hh:mm
mm/dd/yyyy hh:mm:ss
mm/dd/yyyy hh:mm AM
mm/dd/yyyy hh:mm PM
dd/mm/yy
dd/mm/yyyy
dd/mm/yyyy hh:mm
dd/mm/yyyy hh:mm:ss
dd/mm/yyyy hh:mm AM
dd/mm/yyyy hh:mm PM

I need all to be one of the following dd/mm/yyyy or dd/mm/yyyy hh:mm or dd/mm/yyyy hh:mm:ss

Any tips or help would be appreciated
Ace_Crosley_79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NBVCCommented:
If you have that mix in your column, how will you know the difference between

mm/dd/yy

and

dd/mm/yy

formats (i.e. how will you know which it is?)
0
Ace_Crosley_79Author Commented:
Lucky everything above one row is UK and the below is US formats. So that I can work out.
0
Ace_Crosley_79Author Commented:
FYI typo on 3rd line of date formats. mm/dd/yyyy hh:mm
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

NBVCCommented:
Possibly then, for the US times, enter a formula in an adjacent available column...

=DATE(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ","/"),"/",REPT(" ",100)),200,100)),TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ","/"),"/",REPT(" ",100)),100,100)),TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ","/"),"/",REPT(" ",100)),1,100)))+TRIM(MID(A1,FIND(" ",A1),255))

Open in new window

where A1 is the cell with the original text.

Copy down for all US numbers

Now you can copy those results and paste special >> values over the originals.
0
Ace_Crosley_79Author Commented:
The above works for the following formats only

mm/dd/yyyy hh:mm
mm/dd/yyyy hh:mm:ss
mm/dd/yyyy hh:mm AM
mm/dd/yyyy hh:mm PM

mm/dd/yyyy shows as "#VALUE!" (I also have m/dd/yyyy)

This is great so thanks!

However my question was not about a formula to convert the date as I can do that (not as well but I got it). I want to use replace to edit cells without excel then seeing them as a date after.

Put 08/10/2013 23:58 in to a cell that has been formatted as text. Now remove the 20 manually (08/10/13 23:58). Excel does nothing to the cell. Now use find and replace and look for /2013 and replace with /13... Excel will reformat the cell from text to a date.... argh!
0
NBVCCommented:
I understood what your question was.  I am not sure that you can easily do that with your data variation using Replace feature.  That is why I offered the formula alternative and then just copy and paste special over top the original.

The formula to fix the error would be:

=DATE(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ","/"),"/",REPT(" ",100)),200,100)),TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ","/"),"/",REPT(" ",100)),100,100)),TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1," ","/"),"/",REPT(" ",100)),1,100)))+IFERROR(TRIM(MID(A1,FIND(" ",A1),255)),0)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ace_Crosley_79Author Commented:
Thanks that worked on all but the m/dd/yyyy however these are negligible so I can edit to fix.
0
Ace_Crosley_79Author Commented:
Sorry correction. It seems to work on all but had issues with the following for some reason.
9/21/2012
9/23/2012
9/23/2012
9/21/2012
9/20/2012
9/22/2012
9/20/2012
9/23/2012
9/21/2012
9/21/2012
9/26/2012
9/27/2012
9/30/2012
9/27/2012
9/28/2012

Great help thanks :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.