Conversion to Date Format

[mpantalone]

I'm getting an excel extract from a software application we use and the date is in the below format. I've tried multiple ways to convert the date however I still can't get it to convert. Ultimately I would like the date to include the year also which I can concatenate (don't know if that's the best way of doing it) and then display the full date dd/mm/yyyy. All my attempts have failed so far and I would greatly appreciated your support. Thanks

Date

Wed 9/12

Wed 16/12

Wed 13/1

Wed 20/1

Wed 27/1

Wed 3/2

Wed 10/2

Wed 17/2

Mon 23/8

Mon 23/8

Tue 24/8

 

[Phuoc]

Try this

Book1
	A	B
1	Date
2	Wed 9/12	9/12/2024
3	Wed 16/12	16/12/2024
4	Wed 13/1	13/1/2024
5	Wed 20/1	20/1/2024
6	Wed 27/1	27/1/2024
7	Wed 3/2	3/2/2024
8	Wed 10/2	10/2/2024
9	Wed 17/2	17/2/2024
10	Mon 23/8	23/8/2024
11	Mon 23/8	23/8/2024
12	Tue 24/8	24/8/2024
Sheet1

Cell Formulas
Range	Formula
B2:B12	B2	=DATE(2024,RIGHT(SUBSTITUTE(A2,"/",0),2),LOOKUP(31,--MID(A2,5,{1,2})))

 

[Dave Patton]

A clear question complete with expected results would be helpful.
Format the dates to your preferences.
N.B. You can post an example with the forum's tool named XL2BB.

T240203a.xlsm
	A	B
1	Date
2	Wed 9/12	Wed 09-Dec-26
3	Wed 16/12	Wed 16-Dec-26
4	Wed 13/1	Wed 13-Jan-27
5	Wed 20/1	Wed 20-Jan-27
6	Wed 27/1	Wed 27-Jan-27
7	Wed 3/2	Wed 03-Feb-27
8	Wed 10/2	Wed 10-Feb-27
9	Wed 17/2	Wed 17-Feb-27
10	Mon 23/8	Mon 23-Aug-27
11	Mon 23/8	Mon 23-Aug-27
12	Tue 24/8	Tue 24-Aug-27
13
2c

Cell Formulas
Range	Formula
B2:B3	B2	=--(TEXTAFTER(A2," ")&"/2026")
B4:B12	B4	=--(TEXTAFTER(A4," ")&"/2027")

 

[mpantalone]

A clear question complete with expected results would be helpful.
Format the dates to your preferences.
N.B. You can post an example with the forum's tool named XL2BB.

T240203a.xlsm
	A	B
1	Date
2	Wed 9/12	Wed 09-Dec-26
3	Wed 16/12	Wed 16-Dec-26
4	Wed 13/1	Wed 13-Jan-27
5	Wed 20/1	Wed 20-Jan-27
6	Wed 27/1	Wed 27-Jan-27
7	Wed 3/2	Wed 03-Feb-27
8	Wed 10/2	Wed 10-Feb-27
9	Wed 17/2	Wed 17-Feb-27
10	Mon 23/8	Mon 23-Aug-27
11	Mon 23/8	Mon 23-Aug-27
12	Tue 24/8	Tue 24-Aug-27
13
2c

Cell Formulas
Range	Formula
B2:B3	B2	=--(TEXTAFTER(A2," ")&"/2026")
B4:B12	B4	=--(TEXTAFTER(A4," ")&"/2027")

Thanks Dave. That's excellent