A client had dates coming into her report in the format 20150131 when she wanted it in the format 31/01/2015.
The formula I used to convert this made use of the LEFT, MID and RIGHT functions:
In this instance I am using field Date from table Orders_. The RIGHT function looks at the field and takes the rightmost so many characters, so in this case it is taking the last two characters of the date.
The MID function looks for the xth character along and then takes a number of characters starting from there, so in this case it is taking the 5th and 6th characters.
The LEFT function looks for the left most characters in the string and takes the number of characters asked for in this case the first four characters in the string.
To join all the bits together with the / character we need to use joining factors – you can use either & or + as the joining factor and because the / is a string it needs to be enclosed in double quotes.
The result is as shown above. However this is still a string and if I want to find the difference between two dates, for example, I will need to convert it to a date.
Going back to my formula, I add CDATE( at the beginning of the formula and and extra ) at the end of the formula.
The string now becomes a date and I can use it as a date for other formulas, parameter queries, etc.