Need to extract date from string and format field as date to group on

[elsenorjose]

Hello all,

I've been searching through the forum looking for the correct formula so I can extract a date from a text field and I have part of it working but I would like to tweak the formula to take into account NULLs and also to convert the results into a date type and also strip any text that remains as a result. I've used the following from a previous post:

whileprintingrecords;
If IsNull({NCR_Form.Stock Locations}) Then ToText(date({NCR_Form.Date_Closed}), "M/dd/yy")
Else stringvar MyStrDate:=mid({NCR_Form.Stock Locations}, instrrev({NCR_Form.Stock Locations}," ")+1)

This gives me results that are dates (9/7/11) but also some text results (PRG1384A) or NULLs. What I'd like to do is tweak the formula so that if I get any text or NULLs, I'd like to convert it to some dummy date (1900-01-01 or something) and then have only real dates in the result of the formula formatted as date. I am trying to group on the results of the formula to chart data over a period of time so I need the results to be in a date format.

I am using CRXI R2 on a SQL Server 2005 backend.

Thank you

 

[lbass]

Instead of showing a solution that doesn't work, it would be more helpful to see samples of how the string date currently displays. If it is embedded in text, show exactly how the field displays. If it is unclear how the date should be interpreted, also show the format, e.g., dd/MM/yyyy.

-LB

 

[elsenorjose]

Sorry LB. I should have posted some samples. Here are some actual values in the field:

See Purge in Agile
See PRG1384 & PRG1384A
Qty 31 move to MRB
WIP = 162/78/09
9/8/11
9/7/11
9/10/11

The field was originally for notes but the report requester 'repurposed' the field to enter dates for tracking purposes. And she wants me to chart on the dates for weekly trending. So, I'd need to assign some dummy value to the first 4 records above but keep the last 3 records since they are 'real' dates.

Thank you.

 

[lbass]

Try something like this:

if isnull({table.field}) or
not isdate({table.field}) then
date(1900,1,1) else
date({table.field})

Your sample doesn't show any need to strip text. Was it incomplete?

-LB

 

[elsenorjose]

Hmm...those were just a handful of records but now that you mention it, I DID have some records that had dates at the end of some text but they aren't there anymore. Perhaps they've reached some status that removes them from the report. I'll use what you provided and see if it errors out later but for now, it's working just fine. Thank you for your help.