CODEWITHSUNDEEP
crystal-reportscrystal-reports-2008
user973671
user973671

Reputation: 1650

Crystal Report formula field formula that pulls a date out of a string?

I need to extract a date out of a invoice number and subtract one month from the month. For example if a invoice number is I2011101002683 I need to pull out the 2011, four numbers starting a position 1, and then the 10, two numbers starting a position 5. and display the date in a 2011/09 format. Thanks in advance.

Upvotes: 0

Views: 631

Answers (2)

craig
craig

Reputation: 26272

Create a formula field to extract the date:

//{@invoice_date}
//I|2011|10|1002683 --> Date(2011, 10, 1)
Date(ToNumber({Command.InvoiceNumber}[2 To 5]), ToNumber({Command.InvoiceNumber}[6 to 7]), 1)

Add the resulting formula to the canvas; format it as desired.

Upvotes: 0

mechanical_meat
mechanical_meat

Reputation: 169524

One way:

numbervar year_start := 2;
numbervar year_len := 4;
numbervar month_start := year_start + year_len;
numbervar month_len:= 2;

mid({Command.InvoiceNumber},year_start,year_len) + "/" + 
mid({Command.InvoiceNumber},month_start,month_len);

Upvotes: 1

Related Questions