Date Conversion

[metalteck]

What I want to do is convert a number to a date value. Current examples of the numbers are: 11506,12906,20306,21006,122805. I select this field and put it in the report. I create a formula field using the datevalue function, and then attempt to format it.

I'm trying to get this value to be turned into a datevalue, but when I use the function datevalue, the output is completely wrong. Examples of are:
122805 = 23/3/36
111005 = 23/6/30

I can change the position of the numbers, but I want to get them right first.

Please help.

 

[lbass]

Try creating a formula:

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

-LB

 

[metalteck]

I've tried this formula, but for some reason crystal tells me that I must have a month from 1 to 12. I've been trying to work on this, but I have had no luck. Help.

 

[synapsevampire]

Try:

whileprintingrecords;
MyDate := totext({table.value},0,""))
if len(MyDate) = 5
then
cdate(2000+val(right(MyDate,2)), val(left(MyDate,1)),val(mid(datex,2,2)))
else
if len(MyDate) = 6
then
cdate(2000+val(right(MyDate,2)), val(left(MyDate,2)),val(mid(datex,3,2)))

-k

 

[synapsevampire]

Ooops, remnants of LB's formula remained, use:

whileprintingrecords;
MyDate := totext({table.value},0,""));
if len(MyDate) = 5
then
cdate(2000+val(right(MyDate,2)), val(left(MyDate,1)),val(mid(MyDate,2,2)))
else
if len(MyDate) = 6
then
cdate(2000+val(right(MyDate,2)), val(left(MyDate,2)),val(mid(MyDate,3,2)))

 

[lbass]

I still think my formula should have worked. Did you implement it exactly as shown with the totext formatted with "000000"?

-LB

 

[synapsevampire]

Seemd like it would work to me, LB.

I just coded around it in case it did not.

It may also be that they didn't understand how the data was stored, and there were exceptions.

-k