Help with converting m/dd/yyyy to a date that works in a pivot table

[monsterboogie]

I have a data dump that gives me date in this format 9/24/2020 in General format
Excel does not recognize this as a date and I cannot figure out how to convert it
I want to make a pivot table and insert a timeline but it doesnt work - says it doesnt have a field formatted as date
I tried ctrl 1 and changing to date - nope
=VALUE(text) - nope
=DATEVALUE(date_text) - nope
I was able to change the / using substitute formula, but still stuck

I'd prefer not to use text to columns - would prefer something that can be easily automated by plugging in data and then a formula does the rest

Help please

[Pepe Le Mokko]

Select the date range without the header - Data - text to columns - delimited - use comma as delimiter - Next- Select the first column check " Date" and select MDY if not try a variation) - Select the second column and check " Do not Import column - Finish
You now have usable dates

[josephteh]

Some of the cells are already in date format such as cells A17 to A44, you need to modify your formulas:
A2 = IF(ISNUMBER(A2),A2,XXXX) with XXXX being your SUBSTITUTE formula.