0

I have got the following web intelligence formula (Business Objects) which works for most of my data to extract a date from a text field;

=If(Pos([Note]; “R”) > 0 And Length(Substr([Note]; Pos([Note]; “R”) + 1; 6)) 
=6;FormatDate(ToDate(Substr([Note]; Pos([Note]; “R”) + 1; 6);“ddMMyy”);“yyyy/MM/dd”);
“Invalid Date”
)

However the text field I am extracting from can contain other “R” values, and the formula does not account for this. I would like to update this formula to account for values beginning with “R” that are followed only by numeric values. Is there a way of doing this?

Below is some sample data that I am extracting from, its the values 180723 I want to extract after the 'R' in this example. The ‘RDDMMYY’ is normally at the start of the text field but it can be also in the middle which is causing me the issue.

“Please add to Reading list BMS999 crn 1234 Req R180723 Oversize QP34.5.M27 2023 put previous ed on standard loan and withdraw 10th ed (2005) from stock”

Thanks Blowers

Improve this question
2
  • Will it always be "Req RDDMMYY"?
    – Isaac
    Commented Oct 21 at 21:34
  • The text can vary in the note field, so the only constant is "RDDMMYY". Sometimes the note contains "Rec" or "Req" but not directly beside the DDMMYY value I want to extract.
    – Blowers
    Commented Oct 22 at 8:50
Related questions
0
Webi Business Objects formula for last day of current year
0
RunningCount webi Business Objects Formula
1
Date as an input parameter to Webi report
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.