[solved] Dates as DDMMYY to DD Mon Year

[excalibur21]

The fixed length data that I receive shows a data as ddmmyy. I have not been able to find a way to get it to change to a true date eg 200208 show as 20 Feb 2008
thanks all. when I import it I change the cell type to text in order to preserve the leading zeroes. I then do as you all suggested
DATE("20"&RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2)) IN Excel
DATE("20"&RIGHT(A1;2);MID(A1;3;2);LEFT(A1;2)) in Calc

with the cells formatted to an appropriate date type

[kingfisher]

Have you looked at the functions LEFT, MID and RIGHT? You can subdivide the number and enclose the parts within the DATE function. Example: =DATE(RIGHT(A3;2);MID(A3;3;2);LEFT(A3;2))

You can format the cell containing the formula with the menu Format >Cells : Numbers

[acknak]

I don't think there is a way to adjust Calc's automatic date recognition, so that DDMMYY (e.g. "022108") would be recognized as a date and converted to an internal date value on input.

You can, however, use a formula to do the conversion after the data are input, as long as they're input as text values.

Would that work for you?

(kingfisher's example is just what I was thinking of)

[kingfisher]

There is no need for the original data to be entered as text.

[Villeroy]

Is 200208 a number or text value? If text, always 6 chars? Why don't you tell us what you tried and which unexpected result you get? "200208" becomes "23 Feb 2448"? This would explain everything.

=DATE(RIGHT(A3;2);MID(A3;3;2);LEFT(A3;2)) works with fixed chars of 6 decimals only.
I would have thought that this can not work since DATE expects 3 numeric arguments. Apparently DATE converts on the fly.

You won't get 6 digits from all numbers, even when 10208 is formatted to show 010208.
=TEXT(A1;"000000") generates the 6 digit string from where you can proceed with the kingfisher's formula.

[kingfisher]

If a leading 0 is omitted, the example requires amendment, obviously. The DATE function accepts digits extracted from a text entry or from a numerical entry.