Converting a character date to a true date

Monday 10 August 2020 @ 11:50 pm

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 (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:

December 1, 2020
Dec 2020

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

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

One Response to 'Converting a character date to a true date'

  1. MHurwood - September 14th, 2020 at 10:12 pm

    I’d like to add a note that the conversion can be locale specific. Take your example string “12/1/2020”. In fact this can be the 1st of December (American) or the 12th of January (Australia, NZ, UK, every other civilised English-speaking nation on earth. ) On my computer (English/Australian locale) the DateValue function interprets it as the 12th of January, but run the *same* report on an American PC and you’d get the result in your list above.

    I’ve not tested it, but there’s a chance that foreign month names may get recognised if your locale is set to a foreign language. e.g. in a French locale, “1 Févr. 2020” or “1 Février 2020” may both be correctly recognised as the 1st of February 2020.

    And incidentally, that’s another format the rest of the world uses – only North America uses “December 1, 2020”, countries that are D/M/Y also write “1 December 2020” or “1st December 2020”. (And yes, the DateValue function works for the first of those, at least in my locale, but not with “1st” as you’d probably expect.)

Leave a Reply

Recrystallize Pro