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!!!!

 

[Craig1]

shg,
Where doe sthe formula go because I have just pasted your suggestion in and all I got was #VALUE!

Any other idea's what the problem may be??

Thanks in advance

Craig.

 

[shg]

The formula goes in the cell where you want the date to appear, and it references the cell that contains the value 20110321

 

[Norie]

Try this.

=DATEVALUE(TEXT(A1, "00\/00\/00"))

If you actually have a whole column of 'dates' like this you should be able to convert them using Data>Text to columns.

Just select YMD from the dropdown on the last step.

Note with any of these formulas/methods you might need to apply the format you want to the date.

That's because they actually convert the value, not the format.

 

[Rick Rothstein]

Still won't convert.

Any other idea's guys??

Craig.

Check the contents of one of the cells that won't convert to see if it has a trailing "invisible" character such as a space or a non-breaking space (ASCII 160). You can quickly check to see if there is something there by clicking after the end of the text in the Formula Bar and seeing if the text cursor touches the last digit or not.

 

[Craig1]

Sorry gents, I have been using the formula in same same cell as the date.
So the 20110321 is say in A11 and I have copied yor formulas into A11. Do I need to add a helper column to achieve what I need??

Craig.

 

[Norie]

Craig

I think any of the formulas posted so far would go in any other cell than the one with the date you are trying to convert.

You don't need a helper column, all you need to do is change the cell reference.

So if the formula references A1 and the cell with the date is actually A11, change the reference to A11 in the formula.

Also, the cell with the formula result might not display the date as you expect, so you will need to format it as you want it, eg Short Date.

If none of the suggestions work then it's probably because of what Rick mentioned, a 'rogue' character.

That's very common when the data comes from the internet.

 

[Craig1]

Thanks for that guys, sorry it took so long for me to latch on, it's been a long night.

Thanks again.

Craig.

 

[Norie]

Craig

So is it sorted?

 

[Craig1]

Perfect,

Thanks again gents, I don't what I would do without you guys.

Craig.