Extract date from Text

Gregfox

Board Regular
Joined
Apr 12, 2011
Messages
120
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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
If the format is always the same, try:
Excel Formula:
=LEFT(A1,FIND(" ",A1)-1)
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,031
Office Version
  1. 365
Platform
  1. Windows
VBA:

VBA Code:
Dim tx As String
tx = Left(Range("A1").Text, 10)
 
Solution

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
455
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Assuming the content is in cell C3.

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

End Sub
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,829
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
ABCDE
1DateTime
210-Jan-20215:37 PM10/01/21 17:3710-Jan-215:37 PM
3or5:37 PM
4
1d
Cell Formulas
RangeFormula
C2C2=A2+B2
D2D2=INT(C2)
E2E2=MOD(C2,1)
E3E3=C2-D2
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,031
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback. :)
 

Forum statistics

Threads
1,136,845
Messages
5,678,093
Members
419,742
Latest member
Dropzyl88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top