Extract Date formula Only

[jc9972003]

Hello can someone please help me.
I'm exporting a report from a diffrent scource and in one section it has this
"Yesterday ( Oct 1, 2012 12:00:00 AM - Oct 2, 2012 12:00:00 AM )"

Can anyone tell me how I can extract the First date only and paste it to a diffrent cell? In this case it would be Oct 1, 2012 or 10/1/2012
Thank you

 

[Don Guillett]

Hello can someone please help me.

I'm exporting a report from a diffrent scource and in one section it has this

"Yesterday ( Oct 1, 2012 12:00:00 AM - Oct 2, 2012 12:00:00 AM )"



Can anyone tell me how I can extract the First date only and paste it to a diffrent cell? In this case it would be Oct 1, 2012 or 10/1/2012

Thank you

For a formula look in the help index for FIND or SEARCH and incorporate into a MID function.
Or use vba with INSTR function
you are looking for "(" and "-" and then format as date only without time.

 

[zvkmpw]

I'm exporting a report from a diffrent scource and in one section it has this

"Yesterday ( Oct 1, 2012 12:00:00 AM - Oct 2, 2012 12:00:00 AM )"

Can anyone tell me how I can extract the First date only and paste it to
a diffrent cell? In this case it would be Oct 1, 2012 or 10/1/2012

With the string in A1, maybe something likethis would help:
=DATEVALUE(TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",100)),FIND("(",A1)+1,80)))

 

[lhkittle]

Hello can someone please help me.

I'm exporting a report from a diffrent scource and in one section it has this

"Yesterday ( Oct 1, 2012 12:00:00 AM - Oct 2, 2012 12:00:00 AM )"



Can anyone tell me how I can extract the First date only and paste it to a diffrent cell? In this case it would be Oct 1, 2012 or 10/1/2012

Thank you

Try this, should work with both Oct 1 & Oct 12. (single & double digits days)

FYI: If the day is one digit, returns the month, day, year + space after year. Otherwise month, day & just year.

=MID(F14,FIND("(",F14)+2,12)

Regards,
Howard

 

[Ron Rosenfeld]

Hello can someone please help me.
I'm exporting a report from a diffrent scource and in one section it has this
"Yesterday ( Oct 1, 2012 12:00:00 AM - Oct 2, 2012 12:00:00 AM )"

Can anyone tell me how I can extract the First date only and paste it to a diffrent cell? In this case it would be Oct 1, 2012 or 10/1/2012
Thank you

Easily done with a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

Note that this function depends on the date being in the format shown in your example:
Month as a three letter abbreviation
Date as a number from 1-31 (no leading zero's)
Year as a four digit year

If the format can be more variable, we will need to adjust the regular expression to accomodate.


To use this User Defined Function (UDF), enter a formula like

=GetFirstDate(A1)

in some cell.

=================================
Option Explicit
Function GetFirstDate(s As String)
Dim re As Object, mc As Object
Const sPat As String = "\b(?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s+(?:[1-9]|[1-2]\d|3[01]),\s+\d{4}\b"
Set re = CreateObject("vbscript.regexp")
With re
.Global = False
.Pattern = sPat
.ignorecase = True
End With
If re.test(s) = True Then
Set mc = re.Execute(s)
GetFirstDate = mc(0)
Else
GetFirstDate = "No date in proper format"
End If

End Function
==========================================

 

[Ron Rosenfeld]

Hello can someone please help me.
I'm exporting a report from a diffrent scource and in one section it has this
"Yesterday ( Oct 1, 2012 12:00:00 AM - Oct 2, 2012 12:00:00 AM )"

Can anyone tell me how I can extract the First date only and paste it to a diffrent cell? In this case it would be Oct 1, 2012 or 10/1/2012
Thank you

Also, if the date will always occur after the first parentheses, and will always have a format of
month<space>date,<space>year<space>
and there can't be anything else that might cause that format to be ambiguous, then you could extract it with this formula:

=--LEFT(TRIM(MID(A1,FIND("(",A1)+1,99)),FIND(CHAR(1),
SUBSTITUTE(TRIM(MID(A1,FIND("(",A1)+1,99))," ",CHAR(1),3))-1)

 

[Ron Rosenfeld]

Hello can someone please help me.
I'm exporting a report from a diffrent scource and in one section it has this
"Yesterday ( Oct 1, 2012 12:00:00 AM - Oct 2, 2012 12:00:00 AM )"

Can anyone tell me how I can extract the First date only and paste it to a diffrent cell? In this case it would be Oct 1, 2012 or 10/1/2012
Thank you

I misread your post. If the date in your string could be either Oct 1, 2012 or 10/1/2012 then we need to change the regex in my UDF.

The following will accept dates in the format of mmm d, yyyy or, if the date is between 1/1/1900-12/31/2199 or using 2 digit years; and the separators can be dashes, spaces, forward slashes or dots, the following should extract them. The only difference is the regex: sPat.

======================================
Option Explicit
Function GetFirstDate(s As String)
Dim re As Object, mc As Object
Const sPat As String = "\b(??:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s+(?:[1-9]|[1-2]\d|3[01]),\s+\d{4})|\b(0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01])[- /.](19|20|21)?[0-9]{2}\b"
Set re = CreateObject("vbscript.regexp")
With re
.Global = False
.Pattern = sPat
.ignorecase = True
End With
If re.test(s) = True Then
Set mc = re.Execute(s)
GetFirstDate = mc(0)
Else
GetFirstDate = "No date in proper format"
End If
End Function
==================================

 

[Ron Rosenfeld]

Const sPat As String =

Please note that the sPat = line needs to be all on one line, and not split as it may appear in your newsreader.

 

[jc9972003]

Hello can someone please help me. I'm exporting a report from a diffrent scource and in one section it has this "Yesterday ( Oct 1, 2012 12:00:00 AM- Oct 2, 2012 12:00:00 AM )" Can anyone tell me how I can extract the First date only and paste it to a diffrent cell? In this case it would be Oct 1, 2012 or 10/1/2012 Thank you

Thank you all for the help..