DD/MM/YY

[Zubair]

I have a list of dates manually putted need formula to convert it to DD MM YY, please help, in some cases space after coma is available and some time not.

April 16,2021

April 1,2021

DD	MM	YY
16	04	2021
01	04	2021

 

[cortexnotion]

Try

Sub test()

Dim LR1 as long, cell As Range

With ThisWorkbook.Sheets(1)
LR1 = .Range("A" & Rows.Count).End(xlUp).Row
    For Each cell In .Range("D2:D" & LR1)
        With cell
            .Value = .Offset(, -3) & "/" & .Offset(, -2) & "/" & .Offset(, -1)
            .Value = .Value
            .NumberFormat = "dd/mm/yyyy"
        End With
    Next cell
End With

End Sub

 

[Dave Patton]

A variety of potential solutions; try your preference.
Format the dates to your preference

Date and Time 2021.xlsm
	A	B	C	D
1	April 16,2021	16-Apr-21	16-Apr-21	16-Apr-21
2	April 1,2021	1-Apr-21	1-Apr-21	1-Apr-21
3	January 31,2010	31-Jan-10	31-Jan-10	31-Jan-10
4	January 1 ,2010	1-Jan-10
5	January 1, 2011
6
7		Day	Month	Year
8	16-Apr-21	16	4	2021
9	1-Apr-21	1	4	2021
10	31-Jan-10	31	1	2010
11	1-Jan-10	1	1	2010
12	1-Jan-11	1	1	2011
13
8c

Cell Formulas
Range	Formula
C1:C3	C1	=IF(ISNUMBER(A1),A1,--MID(SUBSTITUTE(A1,",",", "),1,99))
D1:D3	D1	=DATE(RIGHT(A1,4),MONTH(1&LEFT(A1,3)),MID(A1,FIND(",",A1)-2,2))
B1:B4	B1	=IF(ISNUMBER(A1),A1,DATEVALUE(SUBSTITUTE(A1,",",", ")))
A8:A11	A8	=B1
B8:B12	B8	=DAY(A8)
C8:C12	C8	=MONTH(A8)
D8:D12	D8	=YEAR(A8)
A12	A12	=A5