Date time format issue

[radicaled]

Monthly I have to take my teams metrics and I'm having a problem with the date time format.
When the date comes like

Please Login or Register  to view this content.

I have no problem. Excel will convert it to

Please Login or Register  to view this content.

But when the date is over 12, like

Please Login or Register  to view this content.

, it stay the same.

It's clearly a format issue. But I tried to change the format several times and nothing works.
Is there anyway to set the location. This date are US format, but my excel is set for Argentina.

Thanks

[AlKey]

Select Cells with the date Go to Format Cells.. Select Custom and type in mm/dd/yyyy h:mm AM/PM and click OK

[Norie]

Do you mean 12 days?

What is your usual date format?

Is the 2nd date being recognised as a date?

You can test for that with ISNUMBER, if it returns TRUE it's a real data, FALSE its' a 'text' date.

[radicaled]

I tried to format it with mm/dd/yyyy h:mm AM/PM but only recognize the dates before 07/12
This is an extract from my spreadsheet.

Please Login or Register  to view this content.

I believe that it doesn't recognize all dates older than 07/12/2013

[Norie]

Try this, select the colum with dates, goto Data>Text to columns..., on the 3rd step selecct MDY from the column data format dropdown and then hit Finish.

Then try formatting the dates as you want.

[AlKey]

I had no problem to convert them to the proper standard. What I did, I copied the cell that I previously formatted and pasted it over Special Format and it worked.

[radicaled]

Sorry, it didn't work.
I even tried to remove the format. When I remove the format to the dates under 07/12 it shows numbers, but when I tried to remove the format from the dates over 07/12 it showed me the same cell without changes.

I have attached an extract from the spreadsheet.
DateTime.xlsx

[AlKey]

How about now. Check the attached file.

[radicaled]

excellent, but what did you do

[Norie]

Try this.

Insert a new column after column A, goto Data>Text to columns..., select Delimited, set Space as the delimiter, on the 3rd step select MDY and click Finish.

The 'real' dates will be untouched but the problem dates will have the time date in one column and the time in another.

Select all the times, copy them, select the dates without times, goto Paste>Paste Special and select Add in the operations section.

You should now have all 'real' dates which you can format as you like.

Repeat these steps for any other column with the problem.

[AlKey]

The dates were entered or copied as text. I just needed to convert them numbers.
Select any empty cell and enter 1
Copy that cell and Paste (Special Values and select Multiply) over the data. this will convert text to numbers. Then apply format.

Please let me know if it worked for you.