Last week I wrote about using IsDate() and DateValue() to convert common date strings into true date values. This week I am using that same method to update some formulas on my formulas page. The advantage of this method over the original formulas is the extra validation provided by the IsDate() function.

But there are one or two extra steps needed to use DateValue() to replace the original logic in Formula #2 and Formula #3. The DateValue() function needs separators like dashes or slashes. When character strings have no separators you need to add them using the Picture() function. If the field is a numeric, you need to convert the field into a string first, and then use Picture() to add the separators.

If the field we are converting is a string with values like this: 20201231

Then the formula would look like this:

Local stringvar z := {Table.TextDate};
z := Picture (z, 'xxxx-xx-xx');
If IsDate (z)
then DateValue(z)

But if the value looks the same but is stored as an 8-digit numeric, then we need to add an extra step:

Local NumberVar y := {Table.NumericDate};
Local StringVar z := Totext (y,0,"");
z := Picture (z, 'xxxx-xx-xx');
If IsDate (z)
then DateValue(z)