Formula to create a date from a cell value

I'm copying data from an ad provider into a spreadsheet.

I need to be able to use date filters on this data.

Some date values from the provider are pasting in as Date, some are pasting in as General (column A).

Even if I drag over those General types and change them to Date, I'm still unable to filter data based on Date values.

Therefore I'm creating a new field (column F) to create a date a field from each of the values I'm pasting into column A.

I need a formula that does this:
- If cell A3 is already a date then replicate value of A3 in F3.
- If cell A3 is not a date then take each of the numerical values in that call and create a date out of it in mm/dd/yyyy format.

Can anyone help with that please?

Date Help.xlsx

They all present correctly for me - they appear to be in dd/mm/yyyy format, which is what we use.

Try this:

1. Select column A.
2. Data ribbon | Text to Columns.
3. Click Next > Next > choose MDY > Finish.

Deleted. Simple formula is wrong approach. Use Text to Columns as advised.

- If cell A3 is already a date then replicate value of A3 in F3.

You don't want to do this!

A date that presents as, for example, 11/02/2022 will be read by your systam as 2nd November, but it isn't: it's 11th February. You'd end up with a lot of incorrect data!

Use the process that Sandy and I have showed you.

Try this in another column dragged down AFTER DOING TEXT TO COLUMNS:

=A3+0

Is that column seen correctly?

You need to run text to columns on the whole of column A.

Share a workbook.

I feel like something must have gone wrong when you executed the text to columns, because an ISTEXT(A3) [copied down] shows that everything from row 5 down is still text and not date/number. I also notice that row 3 is now 11 May 2022 and row 4 is 11 Feb 2022, when I think the original data intended these to be 5 November and 2 November.

Noting the error in AliGW's post (post #3 set date format to MDY) that is correct in Sandy666's post (post #2 set date format to DMY) during the text to columns/step 3 of the text import wizard, I believe that you failed to set the correct input date format when you went through the text import wizard. Start over with the original text data, and pay careful attention on step three of the text import wizard to the actual date format as it is in the text string -- specifically DMY.