Help with formatting date

[Versonol]

Hi, I'm working on a macro to scrape various news articles from various websites. Since they come from different sources, the formats for the dates are currently diverse. I would like to hard code the dates for certain sources into the following format dd/mm/yyyy Eg: 31/10/2022 .
I need assistance formatting the following dates :

Published November 1, 2022

Tue, Nov 01, 2022 - 09:58 PM

October 31, 2022 at 5:15 PM

I'm fine with either an excel function or vba code to format these dates.

 

[Dave Patton]

It would help if you provided information on your system's Regional Settings for dates and a sample with expected results.
N.B. You can post an extract of your information with the forum's tool named XL2BB.

T202211x.xlsm
	A	B	C
1
2	Published November 1, 2022	1-Nov-22
3	October 31, 2022 at 5:15 PM	31-Oct-22	31-Oct-22 17:15
4	Tue, Nov 01, 2022 - 09:58 PM	1-Nov-22
5
6	Published November 1, 2022	1-11-2022
7	October 31, 2022 at 5:15 PM	31-10-2022	31-10-2022 5:15 PM
8	Tue, Nov 01, 2022 - 09:58 PM	1-11-2022
9
3c

Cell Formulas
Range	Formula
C3,C7	C3	=ConvertToDate(TEXTBEFORE(A3," at"))+TIMEVALUE(TEXTAFTER(A3,"at ",1))
B2,B6	B2	=ConvertToDate(TEXTAFTER(A2," "))
B3,B7	B3	=ConvertToDate(TEXTBEFORE(A3," at"))
B4,B8	B4	=ConvertToDate(TEXTAFTER(TEXTBEFORE(A4," -"),","))

The UDF is fairly robust and I hesitate to guess about your system's setup.

UDF follows

Function ConvertToDate(S As String)
If Not IsDate(S) Then
    ConvertToDate = CVErr(xlErrNA)
    Exit Function
End If
ConvertToDate = DateValue(S)
End Function

 

[Versonol]

It would help if you provided information on your system's Regional Settings for dates and a sample with expected results.
N.B. You can post an extract of your information with the forum's tool named XL2BB.

T202211x.xlsm
	A	B	C
1
2	Published November 1, 2022	1-Nov-22
3	October 31, 2022 at 5:15 PM	31-Oct-22	31-Oct-22 17:15
4	Tue, Nov 01, 2022 - 09:58 PM	1-Nov-22
5
6	Published November 1, 2022	1-11-2022
7	October 31, 2022 at 5:15 PM	31-10-2022	31-10-2022 5:15 PM
8	Tue, Nov 01, 2022 - 09:58 PM	1-11-2022
9
3c

Cell Formulas
Range	Formula
C3,C7	C3	=ConvertToDate(TEXTBEFORE(A3," at"))+TIMEVALUE(TEXTAFTER(A3,"at ",1))
B2,B6	B2	=ConvertToDate(TEXTAFTER(A2," "))
B3,B7	B3	=ConvertToDate(TEXTBEFORE(A3," at"))
B4,B8	B4	=ConvertToDate(TEXTAFTER(TEXTBEFORE(A4," -"),","))

The UDF is fairly robust and I hesitate to guess about your system's setup.

UDF follows

Function ConvertToDate(S As String)
If Not IsDate(S) Then
    ConvertToDate = CVErr(xlErrNA)
    Exit Function
End If
ConvertToDate = DateValue(S)
End Function

Thank you! It works perfectly on Excel 365, however, when I use Excel 2016, it gives #VALUE! error instead of the dates.

 

[Dave Patton]

Your profile shows 365
The functions TextBefore and TextAfter are only in the latest version of Excel.