Use Case
User has a specific naming convention for their Lot Numbers: ABC-20231025-20211025 where 20231025 is the expiration date and 20211025 is the production date.
User needs to extract the expiration date with this format: MM/DD/YYYY.
Solution
To achieve the requirement, you can use these formula:
(SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),5,2)) || '/' || (SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),7)) || '/' || (SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),1,4))
TO_DATE(((SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),5,2)) || '/' || (SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),7)) || '/' || (SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),1,4))),'MM/DD/YYYY')
Note: Replace {inventorynumber} with {lotnumber} or the field ID you need to convert.
What to do if you get an Unexpected Error
In some cases an unexpected error might be encountered when you run the search. This happening because there are some lines in the search that are returning "invalid" when trying to convert the string to a date.
For example, the lot number is ABC-20231025-20211325, so the system pulls 20211325, and tries to convert it to a date. 13/25/2021 is an invalid date.
Also, check if there are lot numbers with a different string pattern.
You can add a criteria that would filter only those items with the same string pattern as ABC-20231025-20211025 and a valid date. Try these:
- Formula (Text) starts with 20 --> To make sure the Year starts with 20 (like 2021, 2010, etc)
REGEXP_SUBSTR({inventorynumber},'[^-]+$')
- Formula (Numeric) is less than 12 --> To make sure the month is valid
SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),5,2)
- Formula (Numeric) is less than 32 --> To make sure the day is valid
SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),7)
These criteria will filter the lot numbers and should get rid of the error you are encountering,
-----
If you find this information useful, let us know by reacting or commenting on this post!