Date Format

[vmjan02]

I have the date format in column like mention below, need to change it to the function is not working to give the required out put.
I don't want to change the cell format, as all. Any idea.

INCIDENT_CREATION_DATE​	Output
3/24/2020 0:00​	=TEXT(H2,"dd-mm-yyyy"))
3/24/2020 0:00​	=TEXT(H3,"dd-mm-yyyy"))
3/24/2020 0:00​	=TEXT(H2,"dd-mm-yyyy"))
3/24/2020 0:00​	=TEXT(H2,"dd-mm-yyyy"))
3/24/2020 0:00​	=TEXT(H2,"dd-mm-yyyy"))
3/24/2020 0:00​	=TEXT(H2,"dd-mm-yyyy"))
3/24/2020 0:01​	=TEXT(H2,"dd-mm-yyyy"))

 

[mole999]

the double ) on the end is many times, changing it, also you have H2

 

[vmjan02]

It is still not giving me required out put
=TEXT(H2,"dd-mm-yyyy")

 

[Peter_SSs]

In a vacant cell put this formula and tell us the result

=ISNUMBER(H2)

 

[vmjan02]

=ISNUMBER(H2)

result is as "False"

 

[vmjan02]

I have tried functions
Datevalue but result is as "#VALUE!"
Date(year,month,day) but result is as "#VALUE!"

no luck

 

[mole999]

=TEXT(--H2,"dd-mm-yyyy") before the H2

 

[kdraghu]

Hi,
Suppose your data is in column A, enter =LEFT(A2,9) in column B.
Then apply =TEXT(C2,"dd-mm-yyyy") in column C

 

[MARK858]

How about one of the below?

Book1
	A	B	C
1	INCIDENT_CREATION_DATE	Output1	Output2
2	3/24/2020 0:00	24/03/2020	24/03/2020
3	3/24/2020 0:00	24/03/2020	24/03/2020
4	3/24/2020 0:00	24/03/2020	24/03/2020
5	3/24/2020 0:00	24/03/2020	24/03/2020
6	3/24/2020 0:00	24/03/2020	24/03/2020
7	12/24/2020 0:00	24/12/2020	24/12/2020
8	3/24/2020 0:01	24/03/2020	24/03/2020
9	3/04/2020 0:01	04/03/2020	04/03/2020
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=DATE(RIGHT(TRIM(LEFT(A2,10)),4),LEFT(TRIM(LEFT(A2,10)),FIND("/",TRIM(LEFT(A2,10)))-1),MID(TRIM(LEFT(A2,10)),FIND("/",TRIM(LEFT(A2,10)))+1,2))
C2:C9	C2	=IFERROR(IF(ISNUMBER(TRIM(LEFT(A2,10))),VALUE(TEXT(TRIM(LEFT(A2,10)),"mm/dd/yyyy")),DATE(RIGHT(TRIM(LEFT(A2,10)),4),LEFT(TRIM(LEFT(A2,10)),FIND("/",TRIM(LEFT(A2,10)))-1),MID(TRIM(LEFT(A2,10)),FIND("/",TRIM(LEFT(A2,10)))+1,2))),DATE(RIGHT(TRIM(LEFT(A2,10)),4),LEFT(TRIM(LEFT(A2,10)),FIND("/",TRIM(LEFT(A2,10)))-1),MID(TRIM(LEFT(A2,10)),FIND("/",TRIM(LEFT(A2,10)))+1,1)))

 

[Peter_SSs]

Perhaps local date settings will make a difference or I have not interpreted correctly, but these are my observations.
Post 5 indicates original data is Text.
Post 1 attempted formulas indicate a Text result is required
Post 1 also indicates original text dates are in mdy order but result is required in dmy order.

Based on the above, testing of the various suggestions give for me (with my suggestion in column N) ..
(I didn't bother with cell-formatting columns L:M since I think a text answer is wanted and an error occurs for row 3 anyway.)

20 03 29.xlsm
	H	I	J	K	L	M	N
1		Post 7	Post 8	Post 8	Post 9	Post 9	Post 10
2	3/24/2020 0:00	#VALUE!	3/24/2020	3/24/2020	24/03/2020	43914	24-03-2020
3	3/4/2020 0:00	03-04-2020	3/4/2020	03-04-2020	#VALUE!	#VALUE!	04-03-2020
4	11/14/2020 0:00	#VALUE!	11/14/202	11/14/202	14/11/2020	44149	14-11-2020
Extract & Format Date

Cell Formulas
Range	Formula
I2:I4	I2	=TEXT(--H2,"dd-mm-yyyy")
J2:J4	J2	=LEFT(H2,9)
K2:K4	K2	=TEXT(J2,"dd-mm-yyyy")
L2:L4	L2	=DATE(RIGHT(TRIM(LEFT(H2,10)),4),LEFT(TRIM(LEFT(H2,10)),FIND("/",TRIM(LEFT(H2,10)))-1),MID(TRIM(LEFT(H2,10)),FIND("/",TRIM(LEFT(H2,10)))+1,2))
M2:M4	M2	=IFERROR(IF(ISNUMBER(TRIM(LEFT(H2,10))),VALUE(TEXT(TRIM(LEFT(H2,10)),"mm/dd/yyyy")),DATE(RIGHT(TRIM(LEFT(H2,10)),4),LEFT(TRIM(LEFT(H2,10)),FIND("/",TRIM(LEFT(H2,10)))-1),MID(TRIM(LEFT(H2,10)),FIND("/",TRIM(LEFT(H2,10)))+1,2))),DATE(RIGHT(TRIM(LEFT(H2,10)),4),LEFT(TRIM(LEFT(H2,10)),FIND("/",TRIM(LEFT(H2,10)))-1),MID(TRIM(LEFT(H2,10)),FIND("/",TRIM(LEFT(H2,10)))+1,1)))
N2:N4	N2	=TEXT(REPLACE(SUBSTITUTE(LEFT(H2,FIND(" ",H2)),"/",TEXT(LEFT(H2,FIND("/",H2)-1),"00"),2),1,FIND("/",H2),0),"00-00-0000")