Error resulting from date formatting

[mikec82]

I have a workbook with three sheets - Data, VisitDate1, and VisitDate2.

VisitDate1 takes the date from "Data": =INDEX(Data!H3:R3,MATCH(TRUE,INDEX((Data!H3:R3<>0),0),0)).

The result looks like this:

Mon, Sep 10, 2018 8:30AM - 2:00PM

<tbody>
</tbody>

VisitDate2 should take that date and strip it down to 9/10/18: =REPLACE(LEFT(REPLACE(VisitDate1!A3,FIND(",",VisitDate1!A3,5)-2,2,""),FIND(",",VisitDate1!A3,5)+3),1,5,"")+0

This used to work when the raw data was formatted with a "th" after the day:

Mon, Sep 10th, 2018 8:30AM - 2:00PM Can I revise either the formula in VisitDate1 or VisitDate2 so that the result is 9/10/18? It is currently resulting in an error.

<tbody>
</tbody>

 

[mikec82]

I just ended up adding another sub, shown below and it works now. How would I add additional years to the statement, so that it would change not only "2018", but also 2019, 2020, 2021, and 2022?

Sub DateFormat()
'
' DateFormat Macro
'


'
Sheets("Data").Select
Columns("H:R").Select
Selection.Replace What:=", 2018", Replacement:="th, 2018", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Sheets("Report").Select
End Sub