Help with Text to Date Formula

Copper Contributor

Hello,

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

=DATEVALUE(SUBSTITUTE(A1,MID(A1,7,2),""))

@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