Formatting Issue

[tmanch]

Good Morning All,

Currently I am receiving data from a person in in Mexico via e-mail. We're working in an .xls spreadsheet. I need her to report dates of February as MM/DD/YYYY. The dates are actually showing as DD/MM/YYYY. She showed me a screenshot to confirm they are in the correct formatting, but on my side i'm seeing different. I did confirm the entire column is formatted as date *3/14/2001.

I did notice that when i filter on the date column the rows that have issues show as March, April, May, with a drop down of the number (date). There are multiple other rows that are showing correct as 02/01/2014.

I've added a link here where a picture is attached of the error.
http://i58.tinypic.com/14jnif8.jpg

What could be wrong?


Thanks,
Tom

[Richard Buttrey]

First check by using the function =ISNUMBER(A1) that each 'date' is in fact a proper date number.

If it is then it's just a formatting issue. If not you'll need to go back to source and ensure all 'dates' are in fact proper date numbers.

Upload the workbook for more specific advice.

[tmanch]

I ran =isnumber(cell) on multiple cells. The dates that look correct come up false, but the dates they are DD/MM/YYYY are comming up true. Is there another step you would suggest to make?

Unfortunately i cannot upload the entire workbook due to privacy concerns.

http://i62.tinypic.com/167t7df.jpg

[newdoverman]

Just upload a copy of the date column without any of the other data.

I suspect that the date entry at the source is not consistent. Where the day of the month is 12 or less, who knows if the day and month are reversed or not. Combine that with the possibility that the dates entered are not dates according to Excel.

[tmanch]

I uploaded only the date column that has the issue.

[Richard Buttrey]

Your dates seemed to have changed at A961. If I were to guess I reckon that somehow the data has been downloaded at different times with different dd/mm/yyyy settings that have somehow been changed to text values rather than retaining the original number.

One way of correcting would be to enter

Please Login or Register  to view this content.

in B961, copy it down, then Copy the whole of B961:B2218 back to A961 as values.

[newdoverman]

I had to use Richard Buttrey's formula then select the cells B961:B2218 Format Cells and select Date, Custom and change the format to MM/DD/YYYY. Then copy and paste values AND FORMATS into column A.

If I left out any of the above steps, my Regional Settings took over and gave the DD/MM/YYYY format.

So, if the formula itself doesn't work try the formatting procedure that I followed AFTER applying the formula.

[tmanch]

What i believe is happening is that the invoice dates from row 2-960 are actually formatted in Spanish(Mexico) on their side. When i open the file it automatically gets reversed to US(English). In my role i cannot physically change their invoice date, because it is their responsibility to report the data to me. Anyway, i am having them remove the data from the column, then format it to US English, and enter the data in, again.

Thanks for all your help.

[newdoverman]

If the source of the data, removed the date format just leaving you with the date serial number then all you will have to do is to apply the US date format.

To change from Date format to serial number they should just highlight the column of dates and format as GENERAL. All the dates will be changed to their native serial number.