Text to Date, Convert to US Format from UK, Eliminate Time

[ExcelQuestFL]

Good Morning,

Yes another "date" thread . I have been working on this problem for several hours and haven't been able to resolve. I also searched the forum and I see members using similar formulas, but it's not working for me.

I have Date and Time data in text (as it's right aligned) in my cell..it's in UK format:
1/9/2013 12:00:00 AM

I want my output to be 9/1/2013.

I use "=DATE(MID(A1,5,4),MID(A1,3,1),MID(A1,1,1))"

My result is "2/4/1903"? Is it because time is in the cell too so it's throwing it off?

The other thing is my dates will also have double digits, so I could have 10/9/2013 and I would want output 9/10/2013. My above formula would need tweaking for double digits.

Any assistance is MOST DEFINITELY appreciated at this point...at my wit's end with this.



EDIT - SAMPLE FILE
SAMPLE.xlsx

[Norie]

Select the column with the date/times, goto Data>Text to columns..., select Fixed Width and click Next.

On the next step make sure the date and time are in separate columns and click Next.

On the 3rd step select the column with the date and set it's Column data format to MDY, select the column with time and choose Do not import.

Click finish.

You should be left with 'real' dates that can be formatted as you want.

Actually before you try that, check that what you have isn't already a 'real' date.

That can be done with ISNUMBER.

[ExcelQuestFL]

Thank you. Yes I played around with Text to Columns. I didn't choose that option because I was trying to keep the column with the data unaltered and have the output elsewhere in the sheet.

[Norie]

Why not copy the original data to where you wanted to output it to?

Then do the text to columns.

[ExcelQuestFL]

That solution isn't working for my data. Perhaps I should have mentioned that other data points in my sheet are numerical format. The original formula works well for those.

As an example, "12/9/2013 11:59:00 PM", right aligned. Applying text to data columns results in the date plus 12:00:00, but the formula works in that instance?

I hope this helps..let me know if I need to clarify and I'll upload.

[Norie]

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[ExcelQuestFL]

Hi there, yes I've just uploaded some sample data. Thanks for taking a look.

[Norie]

When I carried out the steps I described for text to columns it worked for me.

The only problem was some of the dates needed reformatting.

[ExcelQuestFL]

When I do it, all the 9/1/2013 dates have "9/1/2013 12:00:00 AM". You didn't get that?

[Norie]

Is that not the date you expect?

Did you try reformatting the dates like that?

By the way, what did you choose for the date format on the 3rd step of text to columns?

No matter what your regional settings it should be DMY.

[ExcelQuestFL]

The value of the output should only be the Date and not include time. HOWEVER, if I reformat, I can eliminate the time. I think I can work with this. Thank you very much for your help!

[Norie]

Like I said the only problem was some dates need reformatting.