Expression Builder Help

[mattc66 via AccessMonster.com]

I have the following expression. When I try this it returns and error. The
LAST_DATE_SOLD is in a YYMMDD format and I want to change it to a MM/DD/YYYY
format.

=Format(DateSerial(Left([LAST_DATE_SOLD],2),Mid([LAST_DATE_SOLD],3,2),Right(
[LAST_DATE_SOLD],2)),"mm/dd/yyyy")

Thanks
Matt

 

[Steve Sanford]

The best way is to convert the "LAST_DATE_SOLD" field to "YYYYMMDD".

According to Help, the "year" argument for the DateSerial function is:

Required; Integer.
Number between 100 and 9999, inclusive, or a numeric expression.


So another way you could to add 1900 or 2000 depending on the YY number.
Maybe any year between 00 and 09, add 2000 else add 1900.

Something like this:

=Format(DateSerial(Left([LAST_DATE_SOLD,2)+iif(Left([LAST_DATE_SOLD,2)>39,1900,2000), Mid([LAST_DATE_SOLD],3,2),Right( [LAST_DATE_SOLD],2)),"mm/dd/yyyy")


HTH