Unified DDMMYY with variable data

[razor_raef]

Hi team,

I'm trying to create a unified formula to give DDMMYY outcome with variable data.

In column A, dates have been pulled from a program which give the formats as attached. Unfortunately the dates are never constant as you can see and trying to create a formula isn't working for me (e.g =TEXT(A1,"DD/MM/YY").

Can anyone please help me in figuring out a simple formula to pull DDMMYY from the variable data?

Thank you in advance

Dates.xlsx
	A
1	8/3/24
2	8/3/24
3	8/3/24 12:00
4	9/3/24
5	9/3/24 12:00
6	9/3/24 12:00
7	10/3/24
8	10/3/24 12:00
9	10/3/24 12:00
10	11/3/24
11	11/3/24 12:00
12	11/3/24 12:00
13	12/3/24
14	12/3/24 12:00
15	12/3/24 12:00
16	13/03/2024 12:00:00 PM
17	14/03/2024 12:00:00 PM
18	15/03/2024 12:00:00 PM
19	15/03/2024 12:00:00 PM
20	17/03/2024 12:00:00 PM
Sheet1

 

[razor_raef]

What version of Excel are you using ? Can you please update your profile on the Forum to show what version you are using since the answer will vary depending on the version.
Try this without using "LET"

=DATEVALUE(IF(ISNUMBER(A2),TEXT(A2,"mm/dd/yyyy"),A2))

I think this formula here just solved it! thank you alex!

 

[Alex Blakenburg]

You're welcome. Glad we could help.