Dates! Dates! Dates!!!

[|ceman]

Hi all,

I'm getting frustrated on the date formats on my worksheet, you see, my data dump came from different sources, some from downloads, some from data sent, etc. etc. the things is, when I try to consolidate them into one dump fil (thru copy/paste) to create a comparative table between dates, the dates have different formats!!!, eg May 2, 2011 appears as figures 40665, some are in dd/mm/yyyy 02/05/2011 and inverse mm/dd/yyyy 05/02/2011 (which is what I'm aiming for) and some are just plain text!!!!, when I create the pivot table, it will not lump all the information under the same dates because they are in different formats. I try the date formatting and it never works so is the custom format. Any quick fix on this? I know there must be something to create a uniform date formats, for now, I have to copy all the dates one by one just to have the same formats which is really frustrating, hope you can help, thanks!!!!

 

[VoG]

Maybe: select the dates, Data > Text to Columns, click Next twice, tick Date and select the format (e.g. DMY) then click Finish.

 

[|ceman]

Hi Vog, thanks for the reply, I tried that already, but the format remains as it is, some did change, but most of it is still the same

 

[kleeb03]

I've had luck by first reformatting the dates to my desired date format. Then Doing a Find & Replace all for the number 2 with the number 2 (and 1 and 1).

 

[xenou]

I don't think there's any quick fix. My preference is to clean up the dates at the time you import them in - preferably in the source data so that there's no question about what happens when it gets into Excel - i.e., if possible convert all dates to a non-ambiguous format such as 01-JAN-2011. I think there are tricks but if you have lots of different data sources you may need to use lots of different tricks - so I think that means taking each kind of problem one at a time and working out the solution until you have a way for tackling for every different kind of problem that comes up.

 

[shg]

Further to xenou's comment, if the dates comes in in different formats, some will be converted incorrectly while others will not convert at all, e.g., if your regional setting are m/d/y, then 1/3/11 will convert incorrectly, and 31/3/11 will not convert.

If you can't correct at the source, import the dates as text and correct them in Excel.

 

[Craig1]

Hi All,

Is there a way of auto formatting 20110321 date (USA I think) into 21/03/2011. When I try to format it comes up as text and all I get is a load of ###### when I click on the 21/03/2011 format.

All help appreciated.

Craig.

 

[Norie]

Try doing the date conversion on the data a 'source' at a time.

ie import one set of data, do the date conversion on it, import the next set and do the data conversion on it and so on.

You could try the different methods available on each set of data to see which one works for that set of data.

 

[shg]

Ypou can't use formatting to convert that to a date, but you can use a formula:

=--TEXT(A1, "0000-00-00")

 

[Craig1]

Still won't convert.

Any other idea's guys??

Craig.