General Formula for Convert text to date function
Using a formula centered on various functions such as LEFT, RIGHT, DATE, and MID, you can analyze and convert a date in an unrecognized format to a proper Excel date.
Figure 1: Converting text to date using the Excel convert text to date function
In the example shown above, the formula used in C7 is:
What this formula does is that it extracts separately the values of year, day and month, with aid of the DATE function, a date is produced – July 28, 2018.
Background Information about the Excel Convert text to date Function
While working on data sourced from a different system, there are possibilities that you may have to work on text values that date but are not written in the format that allows EXCEL to recognize them as dates. For instance, you may have such text values similar to this:
It is obvious that Excel, based on how it was programmed will not accept these texts in this format as dates, so, to create an understandable date, we will need to split the text into its various components (day, month, and year). After this achieved, it becomes easy to use the DATE function to seamlessly produce dates.
How the Convert text to date function works
Three arguments are involved in the DATE, these are day, year and month. In the Excel convert text to date function, the LEFT function removes the 4 characters at the extreme left and provides this to the DATE function as the year. Then the MID function uses 5th and 6th characters gives this to the DATE function to correspond with the month, and finally, the RIGHT function takes off the 2 characters at the extreme and supplies these to the DATE function to serve as the day. Finally, resulting from this argument is an Excel date that can be altered in any manner to suit you.
In row 10, the (unrecognized date) is in the format dd.mm.yyyy and the formula used in C10 is:
Figure 2: Example of Converting text to different date format using the Excel convert text to date function
You can as well add zero to fix dates
You may come across a date in a text format which should be compatible with Excel on a normal day. In this case, when Excel doesn’t accept the date you can force it to do so by adding zero to the text. Once this is done, Excel automatically converts the text into a date since dates are basically numbers. You can apply this simple tricks to convert text to date in Excel.
To convert dates by adding zero in a formula, use:
where D1 contains the unrecognized date.
Text to columns to fix dates
Using the text to Column feature is another way of forcing Excel to recognize dates
- Select the dates column, then click on Data menu > Text to columns > Fixed > finish
- This can fix everything at once sometimes.