String to date convert 3 char day to proper day

[bobpv]

I need to extract and display a date from an order number. The order numbers are XXYYYYDDDAAA with XX being a two letter code for product type, YYYY is the year, DDD is the day of the year with Jan. 1 being 001 and Dec. 31 being 365. The last three characters, AAA are the number of that order for that day.
I can use DATEVALUE like this to pull wha I need:

DATEVALUE(
TONUMBER({FIELD},3 TO 7),
TONUMBER({FIELD},8 TO 10)
)

But how can I convert 001 to read Jan 1st?

Thanks in advance

 

[Turkbear]

Hi,
Look into using the CDate and DateAdd functions:
One format is:
CDate(DateAdd("d", TONUMBER({FIELD},8 TO 10)
, #January 1, 2005#))


Will return the Date based on 1/1/2005...

You may want to use a formula to build your reference date using the Year(Currentdate) to get the year..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Try the following:

dateadd("d",val(mid({table.orderno},7,3)), date(val(mid({table.orderno},3,4)),01,01)-1)

-LB

 

[bobpv]

Thanks!