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: [TABLE="width: 268"]
<tbody>[TR]
[TD="class: xl17, width: 268"]Mon, Sep 10, 2018
8:30AM - 2:00PM[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="width: 268"]
<tbody>[TR]
[TD="class: xl17, width: 268"]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.[/TD]
[/TR]
</tbody>[/TABLE]
VisitDate1 takes the date from "Data": =INDEX(Data!H3:R3,MATCH(TRUE,INDEX((Data!H3:R3<>0),0),0)).
The result looks like this: [TABLE="width: 268"]
<tbody>[TR]
[TD="class: xl17, width: 268"]Mon, Sep 10, 2018
8:30AM - 2:00PM[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="width: 268"]
<tbody>[TR]
[TD="class: xl17, width: 268"]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.[/TD]
[/TR]
</tbody>[/TABLE]