Extract date from Text

[Gregfox]

Hi, I have the following: 01/10/2021 5:37pm ET This of course changes every day, both the time and date and year.
I'm looking for VBA code to extract only the date(in this case 01/10/2021) and be able to cope with the changing dates/time/year.
I've tried lots of different code but can't seem to get it.
I have this: "01/10/2021 5:37pm ET" BUT need this: "01/10/2021"

Thanks!

 

[aRandomHelper]

If the format is always the same, try:

=LEFT(A1,FIND(" ",A1)-1)

 

[Akuini]

VBA:

Dim tx As String
tx = Left(Range("A1").Text, 10)

 

[Saurabhj]

Hi,

Assuming the content is in cell C3.

Sub myMacro()
    Dim myDate As String
    myDate = Left(ActiveSheet.Range("C3"), 10)
    msgbox myDate
    MsgBox Format(myDate, "DD-MMM-YY")

End Sub

 

[Dave Patton]

It would be useful to know if the data is Text or real Dates & Time.
The post did not indicate whether the Dates are USA or International. A guess would be USA.
The logic is the same for USA or International systems but the Dates are formatted different mm-dd-yyyy versus dd-mm-yyyy
There are posts for the VBA and USA style dates.
One example is VBA to extract time only from Datetime format
I accidently posted my reply to that thread.

Date and Time 2021.xlsm
	A	B	C	D	E
1				Date	Time
2	10-Jan-2021	5:37 PM	10/01/21 17:37	10-Jan-21	5:37 PM
3				or	5:37 PM
4
1d

Cell Formulas
Range	Formula
C2	C2	=A2+B2
D2	D2	=INT(C2)
E2	E2	=MOD(C2,1)
E3	E3	=C2-D2

 

[Gregfox]

Thank you for your help, both Akuini and Saurabhj had the solution.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.