I run into many situations where dates are stored as numbers or strings. These need to be converted to true date values before they can be used in date calculations. I have posted formulas on my site that convert common numeric and string values into true dates.

One of the challenges when converting dates is dealing with invalid values. These will generate an error when you attempt to convert them into dates. One way to validate a string input is with the IsDate() function. This checks and see if a string value can be converted into a date before doing the conversion. It is usually partnered with the DateValue() function which converts any valid date string into a true date value. The formula would look like this:

If IsDate ({String.Field})
then DateValue({String.Field})

I did some experimenting with this today and I was surprised at how many different patterns can be converted using these functions. The general rule is that the string needs 2 or 3 separate parts in one of these 5 patterns:

Month-Day-Year
Year-Month-Day
Day-Month-Year
Month-Day (assigns current year)
Month-Year (assigns day as 1)

The year can be 2 or 4 digits, except for Month-Year pattern which MUST have a 4 digit year.
(2 digit years >= 50 are assumed to be 19xx, while those < 50 are assumed to be 20xx)
The month can be a number, the full month name or the 3-character abbreviation.
The separators can be a slash, a dash, a space or a comma (or any combination).

This gives you over 400 different patterns that can be directly converted into a date. Here are just a few common string examples that will all return the date value 12/1/2020 once converted:

12/1/2020
December 1, 2020
2020-Dec-1
1-12-2020
Dec 2020
12/1

For my next post I am going to redo formulas #2 and formula #3 on my formulas page so they use this method.