Excel / Inconsistent Dates not converting to number

Question

Hello! 

I have an (apparently) inconsistent dataset of dates (10000+ rows) formatted in a unknown locale. It looks like dd/mm/yyyy (Afrikaans???).  I do not know the origin of the file I have. The inconsistency appears to be that the day has been typed/inputted with/without a leading zero (example: 01 vs 1, see and compare A3 to A9 for example).

The Date_of_Journey column "seems" to be consistently formatted, but all dates in the column are left-aligned, which tells me they are text. 

See the screenshot below. 

How do I go about consistently converting each date to the correct equivalent in my current locale (mine is: "mm/dd/yyyy")? 

Here is what I have tried: 

Converted column A from Text-to-Columns using "/" as separator. Then concatenated (=CONCAT()) three columns (C,D,E) into G (a string), then Copy-pasted values only into H (to no avail, since a quick edit of individual cells will yield an incorrect date, as shown in H3 [should be May 1, 2019] or H6 [should be March 1, 2019).

Any suggestions, fixes, VBA maybe? TIA

Here is a screenshot of the first few records: 

Here are the last few records of the dataset: 

Answer 1

Use the following formula in F2 and copy down

=DATE(E2, C2, D2)

In your case, E2 is year, C2 is month, and D2 is day of the month.

first parameter is year, second is month, and third is day in the month.

eg.

=DATE(2021,3,15) will give you 15th March, 2021..  you will see the result based on formatting of the cell containing the formula.

Answer 2

Very helpful, and simple. I could not think of it, but should have...mental block? 

Thanks very much!

Answer 3

You are welcome.

Happens to all of us.

Answer 4

Hi Nelson. I’m RN, an Independent Advisor and Microsoft user like you.

May you share a sample file on Onedrive to investigate it further?

Also may I know what is the desire output for the date?

Do you want it as "mm/dd/yyyy" output?