I have been looking online for a formula to convert a text field which displays as a date to a true date or numeric field. The field contains values that range between 09/19/2014, 9/19/14, and even 9/19/14. I unfortunately do not have direct access to the database in which these values reside because I would just format them there.
The reason I need to convert this field is because I have to write a crystal report which can use the values in this field in a data range parameter. Is there a way to convert these values which are not consistent using a formula? I have tried to convert using various ToNumber formulas but I think it runs into trouble because the field is inconsistent in how the text was entered.
Thank you
To add to the comment below this is what i have done so far.
First I created a formula to remove the slashes from the field using, Replace({V_GUIDESHEET.ITEM_COMMENT},"/",""). Then I created another formula to convert the the field to a number Right("0"&{@ReplaceSlash},8). So now the formula field I have left is a number field with values that look like 05272016 or 0692016 which I would like to behave like 05/27/2016 or 06/9/2016 but the key is I need this new formula field to think and act like a date field so that I can create a date range parameter. The user when they open the report need to type in date range in order to display the permits which expire during a specific range.
I have also tried
Mid (ToText ({@ExpirationNumber}, "#", 0), 5,2)
+ "/" + Right ( ToText({@ExpirationNumber}, "#", 0),2)
+ "/" + Left (ToText ({@ExpirationNumber}, "#", 0), 4)
which returns the value 01/16/5272 for the value 5272016.
The bad news is that picking apart the pieces of your date string and creating an actual date out of them is not completely trivial. You would first have to split the string into three new strings pivoted around the '/' character, converting each piece into a number, and then passing each as a separate parameter into the Date() function.
The good news is that CR already takes care of this for you in that the Date() function is overloaded to also accept a single string parameter. It accepts a date string of varying lengths as long as it is well-formed and matches your local date format, and out will pop a datetime value. So Date("1/17/14") would work as well as Date("01/17/2014").
The first step of solving a problem like this is to check the documentation and make sure you understand what functions are available to you and what they do, exactly. For example, the Right() function does not convert a string to a number like you say and would likely cause you even more headaches down the line if you were to try and move forward with that approach.