Forum Discussion
Help with Text to Date Formula
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 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
Depends on regional setting and defined long date format, for US ones could work
=DATEVALUE(SUBSTITUTE(A1,MID(A1,7,2),""))
- 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?
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.
