Help with Text to Date Formula

Occasional Visitor


I have been using many different methods for attempting to convert text and date - but failing miserably!


Essentially, the way I can download data provides dates that appear like this:


Jun 13th 2017 10:44
Oct 03rd 2017 16:18
Jul 03rd 2017 16:10
Dec 21st 2016 15:22
Jul 05th 2017 14:07


Does anyone know a formula and steps involved that I can please use to convert this to appear in dd/mm/yyyy format please?


I can get rid of the time at the end, but every approach I've tried so far just throws up #VALUE error.


I need to do this to show how much time has elapsed since each day until (TODAY). 


Any advice very welcome - thank you!

4 Replies

I suppose it is a string. 


If so, you may consider LEFT and MID function to extract the information about year/month/day. Then, use the function DATE, you can have the date in your formatting. 


Hope that it is helpful.

Do your months normally show in English when you enter a correct date in Excel and format it to show the name of the month?

Depends on regional setting and defined long date format, for US ones could work


@Christopher Barton 


i think that the main issue is the suffix after the day, and lack of a comma between day and year 


I would find/replace or automate that the following 

find: ‘rd’

replace ‘,’


find: ‘st’

replace ‘,’


find: ‘nd’

replace ‘,’


on my end it changed to a date as soon as I did that