TEXTDATE

Re-construct a date from a text string (which contains a date in a non-standard format which Excel does not recognize as a valid date).

Usage

Very often, when dates are import from external sources, for example, from data files of other applications, those dates are not in standard date format that can be recognized by Excel as DATES. They are often recognized by Excel as TEXT. It could be extremely difficult to perform calculations / analysis.

Common non-standard date formats include:
“ 28/02/2003 12:00:00.000 AM”
“ 20030228”

TEXTDATE function quickly translates such non-standard dates into date values that can be recognized by Excel.

Syntax

TEXTDATE(string_input, yr_pos, month_pos, day_pos, yr_len, month_len, day_len)

string_input is the text string which contains the date you want to re-construct.
yr_pos is the starting position of the year.
month_pos is the starting position of the month.
day_pos is the starting position of the day.
yr_len is the length (number of characters) of the year.
month_len is the length (number of characters) of the month.
day_len is the length (number of characters) of the day.

Remark

After typing the formula into a cell, you may need to set the cell number format to a “Date” format.

Example