Date conversion

[Elvio]

I have an external data source that has a date format of YYMMDD as text.

(do you know what this format is called?)

Anyway, I'm converting this to a regular date format of DDMMYY (ie non-American). I've managed to do it in a complex formula.

Is there a simple method?

[Vaughan]

I'd suggest converting it into a DATE field using the Date(month, day, year) function, then you can *display* the date any way you want!

Left(datetext, 2) is the year, or at least the last two digits.

Middle(datetext, 2, 2) is the month.

Right(datetext, 2) is the day.

Having the text in YYMMDD format is a bit of a bummer, because you are going to have to guess what the century is.

To work out what the century is, lets make some simple rules: if the number is greater than 50 then it's 19XX, else it's 20XX. This would be:

TextToNum(Case(TextToNum(Left(datetext, 2)) > 50, "19","20") & Left(datetext, 2))

I haven't tested it but this should be close.

The whole thing will therefore be:

code:

---

Date (

TextToNum(Middle(datetext, 2, 2)),

TextToNum(Right(datetext, 2)),

TextToNum(Case(TextToNum(Left(datetext, 2)) > 50, "19","20") & Left(datetext, 2))

)

---