Recognize datetime in 20211124_1502 format with Excel

I have a column in an Excel table with datetimes in this format:

20211124_1502

I tried different formatting options for these cells (Date, Short date, etc.) but the date is never recognized/parsed (then I cannot filter by months with Autofilter, etc.)

Is there a simple way for Excel to automatically recognize these datetimes? (without having to use a second column with a formula to manipulate and parse the strings from this column).

Solution 1:

There is NO way excel will recognize that text string as a date. It needs to be manipulated in a second column (or in place using VBA) to something Excel can recognize as a date. Then you will be able to format it.

One method: Use a formula to convert that text string to an actual Excel Date.

=--(TEXT(--SUBSTITUTE(A1,"_",""),"0000\/00\/00 00\:00"))

Then you can format it as a date however you want,