Extract Data to be Date Format (dd-mm-yyyy)

[muhammad susanto]

hi all..

how to figure out this problem:

original list	after extracted (dd-mm-yyyy)
ND-60 05 Desember 2024	05/01/2024
ND-65 19 Desember 2024	19/12/2024
ND-564 19 Desember 2024	etc…
ND-08 06 Februari 2024
ND-13 05 Maret 2024
ND-22 01 April 2024
ND-176 01 April 2024
ND-29 06 Mei 2024
ND-35 06 Juni 2024
ND-44 04 Juli 2024
ND-348 04 Juli 2024
ND-52 06 Agustus 2024
ND-63 04 September 2024
ND-69 04 Okt 2024
ND-535 04 Okt 2024

i hope someone would help me out this situation
date format in dd-mm-yyyy

sst.

 

[muhammad susanto]

hi. not accept date but as text (General format)

 

[Cubist]

What about "05 December 2024"?
The English version.

 

[muhammad susanto]

to be 05/12/2024 (in date format)

 

[Cubist]

It looks like your system understands English rather than Indonesian- despite the regional settings. Essentially you'd need to translate the months into English version. I don't have a solution on top of my head but will think about it...Someone might offer you a solution before then.

 

[Cubist]

I think this should work in 2021.

Book1
	A	B	C	D	E
1	original list	after extracted (dd-mm-yyyy)		Januari	January
2	ND-60 05 Desember 2024	12/5/24		Februari	February
3	ND-65 19 Desember 2024	12/19/24		Maret	March
4	ND-564 19 Desember 2024	12/19/24		April	April
5	ND-08 06 Februari 2024	2/6/24		Mei	May
6	ND-13 05 Maret 2024	3/5/24		Juni	June
7	ND-22 01 April 2024	4/1/24		Juli	July
8	ND-176 01 April 2024	4/1/24		Agustus	August
9	ND-29 06 Mei 2024	5/6/24		September	September
10	ND-35 06 Juni 2024	6/6/24		Oktober	October
11	ND-44 04 Juli 2024	7/4/24		November	November
12	ND-348 04 Juli 2024	7/4/24		Desember	December
13	ND-52 06 Agustus 2024	8/6/24		Jan	January
14	ND-63 04 September 2024	9/4/24		Feb	February
15	ND-69 04 Okt 2024	10/4/24		Mar	March
16	ND-535 04 Okt 2024	10/4/24		Apr	April
17				Mei	May
18				Jun	June
19				Jul	July
20				Agu	August
21				Sep	September
22				Okt	October
23				Nov	November
24				Des	December
Sheet5

Cell Formulas
Range	Formula
B2:B16	B2	=LET( a,A2, f,FIND(CHAR(160),SUBSTITUTE(a," ",CHAR(160),{2,3})), indo,MID(a,INDEX(f,1)+1,SUM(f*{-1,1})-1), s,SUBSTITUTE(a,indo,XLOOKUP(indo,$D$1:$D$24,$E$1:$E$24)), --RIGHT(s,LEN(s)-FIND(" ",s)) )

 

[HighAndWilder]

Without having to use a lookup table. A bit clumsy I admit but it works.

Format as DD/MM/YYYY

Assumes that the data starts in A2. Change cell reference as appropiate.

Excel Functions.xlsm
	A	B
1	original list	after extracted (dd-mm-yyyy)
2	ND-60 05 Desember 2024	05/12/2024
3	ND-65 19 Desember 2024	19/12/2024
4	ND-564 19 Desember 2024	19/12/2024
5	ND-08 06 Februari 2024	06/02/2024
6	ND-13 05 Maret 2024	05/03/2024
7	ND-22 01 April 2024	01/04/2024
8	ND-176 01 April 2024	01/04/2024
9	ND-29 06 Mei 2024	06/05/2024
10	ND-35 06 Juni 2024	06/06/2024
11	ND-44 04 Juli 2024	04/07/2024
12	ND-348 04 Juli 2024	04/07/2024
13	ND-52 06 Agustus 2024	06/08/2024
14	ND-63 04 September 2024	04/09/2024
15	ND-69 04 Okt 2024	04/10/2024
16	ND-535 04 Okt 2024	04/10/2024
Sheet4

Cell Formulas
Range	Formula
B2:B16	B2	=DATEVALUE(LEFT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Mei","May",1),"Agustus","Aug"),"Okt","Oct"),"Des","Dec"),FIND(" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Mei","May",1),"Agustus","Aug"),"Okt","Oct"),"Des","Dec"),1),100),RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Mei","May",1),"Agustus","Aug"),"Okt","Oct"),"Des","Dec"),4),""),7)&" "&RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Mei","May",1),"Agustus","Aug"),"Okt","Oct"),"Des","Dec"),4))

=DATEVALUE(LEFT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,"Mei","May",1),"Agustus","Aug"),"Okt","Oct"),"Des","Dec"),FIND(" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,"Mei","May",1),"Agustus","Aug"),"Okt","Oct"),"Des","Dec"),1),100),RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,"Mei","May",1),"Agustus","Aug"),"Okt","Oct"),"Des","Dec"),4),""),7)&" "&RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,"Mei","May",1),"Agustus","Aug"),"Okt","Oct"),"Des","Dec"),4))

 

[muhammad susanto]

hi guys, thank you so much..
its helped me.