Number to Date MMDDYY 1

[kcoleman26]

Crystal XI

I have a date field that is a number 12207 = 1/22/07 and 123107 = 12/31/07. How can I get {Date} to show as mm/dd/yy? I saw in this for to convert a sting to date but my field is a number and the ones I found the number was 20070122 so the formula didn't work.



KC

 

[lbass]

Try this:

stringvar x := totext({table.number},"000000");
date(2000 + val(right(x,2)),val(left(x,2)),val(mid(x,3,2)))

This will return a date field that you can format however you like (format field->date).

-LB

 

[kcoleman26]

Thank you for such a quick response.

If you have time and don't mind explaining what makes that work. If not no biggie I will reseach it later just thought I would ask.

Thanks,
KC

 

[lbass]

The conversion totext, allows the field to return 6 digits always. The left, mid, and right functions extract the sets of digits that you need for the date(2000+yy,mm,dd). The date functions reads numbers, so the val() function converts the extracted 2-digit strings back to numbers.

-LB