Date format in Access or even Excel

[M3L155A]

I need to convert dates like this "82009" (20th Aug 09) into a proper date format. Any tips would be appreciated. Thanks

[NeoPa]

Melissa,

looking at your string it seems clear that there is no easy way to determine what is intended. The relevance of the second column can only be by reading the data first. Consider dates :

20 Aug 2009   82009
3 Nov 2009    11309 (or is that 13 Jan 2009)
10 Dec 2009   121009
9 Jan 2009    1909

Can you see why this method of displaying dates is entirely inadequate. Line #2 particularly, shows why it's sometimes impossible to determine the date correctly when presented with it in this format. This is why the "/" character is often used. There are other viable ways of storing/displaying date data, but this isn't one of them.

Welcome to Bytes!

[M3L155A]

Managed to sort it!

Thanks NeoPa,

I see your point. I managed to sort it out. I know it's in US format, therefore I separated the 5 digits and 6 digits and used to following script accordingly, making slight chages, in Excel.

=(MID(A2,2,2)&"/"&LEFT(A2,1)&"/"&RIGHT(A2,2))

[NeoPa]

It doesn't seem that would work reliably for all the data I posted Melissa.

Is the format you're dealing with one you're stuck with?

Does whoever decided to formulate the data that way realise the problems they've caused?

[M3L155A]

Hi, well the date is in the US format so I went with it this way, using the code and amending it when there's 6 or 5 digits. And it's just a one off for now, but I have asked for the data to be re-sent so I can do a comparison, just to make sure that it worked correctly and well use the original as it's correct.

[NeoPa]

US format is m/d/(yy)yy. Not mdyy. US format would avoid all the problems we've discussed.

[M3L155A]

Ok, probably best I get a new output then and avoid all problems encountered previously. Thanks

[NeoPa]

If you can get them to change the format they give it to you in that would make it pretty straightforward .

We're more than happy to help further if you need any at that point.

[M3L155A]

Thanks again for pointing out the bad data format!

Hi, Well the internal person who gave me the data, to prepare for import, said it was in US format. I contacted the agent myself and found out it was in month/year. After looking into it further, one filed had the month/year and another the ddmmyy format. Therefore it's all been sent back and I'm waiting for a brand new file. Thanks again, this would have caused a lot of problems if it was not picked up.

[NeoPa]

You're welcome Melissa.

That actually makes much more sense. Obviously the same format should be used throughout, but the myyyy format could certainly be viable if that's what they end up with. Four-digit years are always to be recommended where possible too of course, which this seems to do fine.