Convert variety of text entries to date in Excel

Question

I have tried a number of the suggested solutions on StackExchange for converting text to date in Excel, however none of them work reliably for the data I have, please see below for explanation.
I have a column with a "start date" in it.  It contains date in the following forms:
(Start state)
9/17/2019 9:30:59 AM
10/20/2020 4:45:59 PM
2/26/2020 12:20:59 PM
This image shows data and the desired end state...
I want to convert the entire entry into an Excel date value so I can do useful calculations on it, e.g. subtract a start time on e.g. 20/11/2020 from an end time on 21/11/2020, so e.g. I want the second entry above to end up as (preferably) 23/02/2020 20:31:59 (because I am a Brit), but even if I can get it to the US version, that's fine, I can work with that.
(Desired end state)
17/09/2019 09:30
20/10/2020 16:45
26/02/2020 12:20
Please don't point me at other answers that appear to solve this.  I have tried using them.  Each appears to have an issue, and I cannot see how to resolve this, or I would not have posted this question.
e.g. If I try =datevalue(left(a1,9)) then it does not get entries with a double figure month (i.e. 10, 11, 12).
If I try the =date(mid.... etc, same thing: it does not reliably grab all the information because the different cells have different lengths of information in.
I have used the Text-To-Columns method, which splits it into non-date/time format cells and lops off the PM or AM into another column which I can discard, BUT this means that instead of 20:31:59 I get 08:31:59 in one column and PM in another, and I cannot see how to get this to return 20:31:59.
If anything in this question is not clear, please ask.
And if I have missed something really obvious I apologise, but I have spent hours trying to solve this.

Mobus · Answer

Ok, so I think I might have a proper solution for you. It would seem the way that Excel converts text to date is dependent on your Windows region settings. Setting the cell's date formatting in Excel only changes how date values are displayed, not how its converted in the first place.
My region settings in Windows is currently set to South Africa. My spreadsheet resolves to this, with the adjacent formula =A1+0 :

Now, if I head to Windows control panel > Change date, time or number formats and temporarily change the format to English (United States), then go back to the spreadsheet and re-evaluate the formula (hit ctrl+alt+F9 if it doesn't auto update):

Now, copy and paste as values the new date value column on top of itself. Save the file. Change your region settings back the way it was. Select the column and file the date & time cell format you like under "custom":

So that:

Mobus · Answer

My first try would be to try and match a custom date format on the text cell. If it works the text in the cell will indent by one space and you will be able to say =A1+0 to add zero days to the date without throwing an error. Then reformat that new cell according to your liking.
Worst case you can chop up the string into a native date format that excel understands. You dont need fixed lengths but can use the space and / characters as delimiters e.g. to get the day inbetween the two /'s
=mid(A1,search("/",A1)+1,search("/",A1,search("/",A1)+1)-1-search("/",A1))

Which semantically means
=mid(A1, from first "/" position +1, with length of second "/" position -1 - first "/" position)

Give or take a +1 or -1. I am not infront of my computer now to test the formula. But if it returns e.g. one character to little just add a +1 to the length argument. Once you have isolated the day month and year (and time if you need to) your can reconstruct a date with the date function.

Convert variety of text entries to date in Excel

2 Answers

Add your own answers!

Ask a Question