Date fields not responding to formatting or macro

[M00N]

Hi,
I have file with over 10 000 lines containing 4 columns with dates. I need to format them to MM/DD/YYYY and do some filtering on the data to do what I need to do with the file.

What I tried so far and what the result was:
* formula '=datevalue' - result was #VALUE! (any other formulas involving dates come out the same as well)
* Date --> Text to columns --> Delimited --> Next --> Tab --> Next --> Column Data format = General --> Finish - this formated SOME but not all of the fields
* Macro which enters the cell, 'doubleclicks' on it and moves to the one below - no result
* Changing regional settings from british english to US english or German - no change
* reinstalling Excel - no change

However - my colleague is able to apply Text to columns or the above mentioned macro and it works on his pc. But even after that I could not format the columns to the same format (MM/DD/YYYY). Instead of 12/01/2010 I still see 01/12/2010. The value is 1st December 2010.
Some values formatted and some didn't trouble is I don't know which and how to get them format all.

We compared our excel settings and found no difference.

So far I tried these solutions with same file on about 6 different computers and it worked on 2 of them (one is the above mentioned where we compared settings, the other is located in Germany and is using German regional settings), 4 (one Austrian German settings, three British English) had same result as mine.

This issue started when this file changed source - it is a company database extract and the databases changed lately. The previous one wasn't a problem, it is only now with the new one. As far as I know the extract is done in excel as well.
Another thing that has changed at the same time was the person generating these reports - before it was done in UK but now all problematic reports were pulled by a person in AMS. Not sure if any of this plays any role.

Any idea what the cause of this is might be? I cannot afford manual editing of these cells, I get a file every month which I need to work with - it ranges from 8thousand to 35 thousand lines.

Let me know if a sample file would be needed.

Any help is very much appreciated!
M00N

[xenixman]

Sounds like mixing System Times.

If you are using a System Time of English (United States), then 13/12/2010 is an invalid date and will throw up an error when using =DATEVALUE(), the #VALUE error is because in the US it's an invalid Argument in the formula.
Also, doing a Text to Columns will fail.

But if you are in the UK using a System Time of English (UK) it should be OK.

Try this, select a large portion of your dates and do a Format, Numbers, they should all display as a 5 digit number. Those that do not are the ones that will cause you grief.

It appears that who ever is now sending you the data has their System Time set up differently.
Best bet is to find out how their set up.

[M00N]

Hi,
Thanks for looking into this.

I tried our advice and interestingly enough all values changed to numbers. I did some more playing with the file and found out something that makes me think it is an issue with how the file is created.
Example:
I format these to MM/DD/YYYY and they look like below however their value means:
01/06/2010 - 1st June 2010
12/15/2010 - 15th December 2010

If I apply DD/MM/YYYY formatting then they swap and I have a different group of values incorrect.

I am now checking with the creator of the file however I am not expecting too much insight, his excel knowledge is limited. But I may find out something. I will post it here if I do.

Also if anyone can think of anything else I will be very happy to listen. I am starting to have a hunch it will be some sort of really silly small detail.

Thank you and I wish you nice weekend.
M00N

[M00N]

Hi,
Just to let you know - tomorrow another report is coming so will see if this same thing will happen. But it would seem the issue is caused by the data from the system and not excel.
So I guess I will close this.

Many thanks for help.
M00N

[martindwilson]

its unlikely he source data will have mixed dd/mm mm/dd values but when opening directly in excel it may try to resolve them. what format is the source data?