Change Number to a date

[djieon]

Hi All, I have a number field called {PRODUCTS.DATLO}. This is a number field but its actually a date. I have tried to convert it using some of the other formulas found on this site but its falling over because if the day is less than ten then it does not print the first '0' and so when I try to run the mid function for the month it falls over.

Some examples of the data:-

10211
10408
10411
241005
241296
290708
290806
40111
40210
40703

any ideas on how to make a standard date out of the above data.

Thanks in advance.

David.

 

[IanWaterman]

Test length first

If length(yourfield) = 6
then
date(tonumber(right(yourfield,2))+2000, tonumber(mid(yourfield,3,2)), tonumber(left(yourfield,2)))
else
date(tonumber(right(yourfield,2))+2000, tonumber(mid(yourfield,2,2)), tonumber(left(yourfield,1)))

Ian

 

[djieon]

HI Ian, this does not appear to be working as its saying it needs text fields?

Any ideas?

Thanks.

David.

 

[IanWaterman]

Sorry my mistake I thought it was a string field when you said Mid was not working

date(tonumber(right(totext(yourfield, "000000"),2))+2000, tonumber(mid(totext(yourfield, "000000"),3,2)), tonumber(left(totext(yourfield, "000000"),2)))

Ian

 

[MCuthill]

Ian,

Wondering about date example 5 "241296" -- I would assume this to be 1996, not 2096.

Would one create a check for a year greater than current and subtract 100 if so? (Or only add 1900 originally)

Leveraging Ian's formula, perhaps:

IF (tonumber(right(totext(yourfield, "000000"),2))+2000)> Year(CurrentDate) THEN
date(tonumber(right(totext(yourfield, "000000"),2))+1900, tonumber(mid(totext(yourfield, "000000"),3,2)), tonumber(left(totext(yourfield, "000000"),2))) ELSE
date(tonumber(right(totext(yourfield, "000000"),2))+2000, tonumber(mid(totext(yourfield, "000000"),3,2)), tonumber(left(totext(yourfield, "000000"),2)))

Of course, I could be off my rocker, as I have yet to have my morning java.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[IanWaterman]

Good catch Mike, that's the trouble with skim reading you miss the details ;-)

Ian