dates within a string

[kmboucher]

good evening,

In my rows I have a column in my worksheet that has the folowing
information and meaning:

D21="PAYE 2004"
D22="SUPPS A 2005"
D23="SUPPS B 2005"

PAYE - represents objects sold between Jan 1 and March 31 plus year in
which sold
SUPPS A - represents objects sold between April 1 and July 31 plus
year in which sold
SUPPS B - represents objects sold between August 1 and Dec 31 plus
year in which sold

the way I have written the the text and date in these cells are the
following

D21="PAYE "&TEXT(DATE(2004,1,1),"yyyy")
D22="SUPPS A "&YEAR(DATE(2005,6,1))
D23="SUPPS B "&YEAR(DATE(2005,10,15))

what I am looking to achieve is to have an another cell in the same
row deriving a date from the information
in that cell, D21, such as february 1, 2004 or from d22 june 1, 2005.
Any help with this problem
would be greatly appreciated!

Revopst

 

[Guest]

You're not going to be able to get it directly from D21, D22, or D23. The
only way I can think of is to extract the formula from those cells and then
get the date out of that. Is there some reason you don't have the dates
stored elsewhere and then reference them for D21, D22 and D23? That would
be far easier.

 

[Guest]

What would make your life a whole lot easiar is in the cell where you want
June 1, 2005... is to put June 1, 2005 in that cell. And then in the other
cell put.

="PAYE "&YEAR("CellWithDate")

 

[kmboucher]

You're not going to be able to get it directly from D21, D22, or D23. The
only way I can think of is to extract the formula from those cells and then
get thedateout of that. Is there some reason you don't have the dates
stored elsewhere and then reference them for D21, D22 and D23? That would
be far easier.












- Show quoted text -

The best results I have gotten so far is with this:

=DATE(YEAR(VALUE(TRIM(MID(D21,FIND("PAYE ",D21)+1,255)))),1,1)

all I get using this format ([<2100]####;yyyy) is

1828

To answer your question, no. There is no easy way out as I am working
from summary sheets. My objective is to find a middle date within the
period given the two parts of information PAYE, 2004. I thought it
would be easy or at least possible for excel to derive feb 01, YYYYY
from paye YYYY within a single cell and not have to use 6 columns.

thanks for the prompt reply!
Revopst

 

[Ron Rosenfeld]

the way I have written the the text and date in these cells are the
following

D21="PAYE "&TEXT(DATE(2004,1,1),"yyyy")
D22="SUPPS A "&YEAR(DATE(2005,6,1))
D23="SUPPS B "&YEAR(DATE(2005,10,15))

what I am looking to achieve is to have an another cell in the same
row deriving a date from the information
in that cell, D21, such as february 1, 2004 or from d22 june 1, 2005.
Any help with this problem
would be greatly appreciated!

If I understand you correctly, you wish to have a formula which will extract
the date you have entered in the DATE function argument. So in the examples
you give, you will extract:

January 1, 2004
June 1, 2005
October 15, 2005

That being the case, you will need to use a VBA Function (User Defined
Function or UDF).

To enter this function, <alt-F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window
Insert/Module and paste the code below into the window that opens.

To use the formula, enter it in some cell as

=GetDt(cell_ref)
or, in your case,

=GetDt(D21)

Format that cell as mmm dd, yyyy

================================================
Function GetDt(rg As Range) As Date
Dim oRegex As Object
Dim oMatchCollection As Object
Dim Y As Integer, M As Integer, D As Integer

Set oRegex = CreateObject("VBScript.RegExp")
oRegex.Pattern = "(\(DATE\()(\d{4}),(\d{1,2}),(\d{1,2})"

Set oMatchCollection = oRegex.Execute(rg.Formula)

Y = oMatchCollection(0).submatches(1)
M = oMatchCollection(0).submatches(2)
D = oMatchCollection(0).submatches(3)

GetDt = DateSerial(Y, M, D)

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


The routine will return a #VALUE! error if there is no DATE function with
numeric arguments in cell_ref.

On the assumption that you might want to substitute cell references for those
arguments to the DATE function, I expanded the GetDt routine:

=========================================
Option Explicit

Function GetDt(rg As Range) As Date
Dim sFormula As String
Dim oRegex As Object
Dim oMatchCollection As Object
Dim YMD(1 To 3) As Integer
Dim i As Long
Dim Temp As Variant

sFormula = rg.Formula

Set oRegex = CreateObject("VBScript.RegExp")
oRegex.Pattern = "(\(DATE\()(.*?),(.*?),(.*?)\)"

Set oMatchCollection = oRegex.Execute(rg.Formula)

For i = 1 To 3
Temp = oMatchCollection(0).submatches(i)

If IsNumeric(Temp) Then
YMD(i) = Temp
Else
YMD(i) = Range(Temp).Value
End If
Next i

'Sanity Check

If YMD(1) < 1901 Or YMD(1) > 2200 Then
GetDt = Error(xlValue)
Exit Function
End If

GetDt = DateSerial(YMD(1), YMD(2), YMD(3))

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

This function should do what you want. However, if the year is not in the
range of 1901-2200, it will give a #VALUE! error.

I chose 1901 for the earliest allowable year because there is a difference in
how Excel interprets dates, compared with VBA, prior to March 1, 1900. This
could be handled in code, if necessary, but it probably isn't.

You can change the upper allowable year to anything up to 9999.

I did not check the values for month and day, as Excel will accept arguments
that are not in the range of 1-12; 1-31 -- merely doing the appropriate math to
change them to a valid date. For example:

DATE(2000,-3,0) --> 31 AUG 1999



--ron

 

[Ron Rosenfeld]

If I understand you correctly, you wish to have a formula which will extract
the date you have entered in the DATE function argument. So in the examples
you give, you will extract:

January 1, 2004
June 1, 2005
October 15, 2005

That being the case, you will need to use a VBA Function (User Defined
Function or UDF).

To enter this function, <alt-F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window
Insert/Module and paste the code below into the window that opens.

To use the formula, enter it in some cell as

=GetDt(cell_ref)
or, in your case,

=GetDt(D21)

Format that cell as mmm dd, yyyy

================================================
Function GetDt(rg As Range) As Date
Dim oRegex As Object
Dim oMatchCollection As Object
Dim Y As Integer, M As Integer, D As Integer

Set oRegex = CreateObject("VBScript.RegExp")
oRegex.Pattern = "(\(DATE\()(\d{4}),(\d{1,2}),(\d{1,2})"

Set oMatchCollection = oRegex.Execute(rg.Formula)

Y = oMatchCollection(0).submatches(1)
M = oMatchCollection(0).submatches(2)
D = oMatchCollection(0).submatches(3)

GetDt = DateSerial(Y, M, D)

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


The routine will return a #VALUE! error if there is no DATE function with
numeric arguments in cell_ref.

On the assumption that you might want to substitute cell references for those
arguments to the DATE function, I expanded the GetDt routine:

=========================================
Option Explicit

Function GetDt(rg As Range) As Date
Dim sFormula As String
Dim oRegex As Object
Dim oMatchCollection As Object
Dim YMD(1 To 3) As Integer
Dim i As Long
Dim Temp As Variant

sFormula = rg.Formula

Set oRegex = CreateObject("VBScript.RegExp")
oRegex.Pattern = "(\(DATE\()(.*?),(.*?),(.*?)\)"

Set oMatchCollection = oRegex.Execute(rg.Formula)

For i = 1 To 3
Temp = oMatchCollection(0).submatches(i)

If IsNumeric(Temp) Then
YMD(i) = Temp
Else
YMD(i) = Range(Temp).Value
End If
Next i

'Sanity Check

If YMD(1) < 1901 Or YMD(1) > 2200 Then
GetDt = Error(xlValue)
Exit Function
End If

GetDt = DateSerial(YMD(1), YMD(2), YMD(3))

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

This function should do what you want. However, if the year is not in the
range of 1901-2200, it will give a #VALUE! error.

I chose 1901 for the earliest allowable year because there is a difference in
how Excel interprets dates, compared with VBA, prior to March 1, 1900. This
could be handled in code, if necessary, but it probably isn't.

You can change the upper allowable year to anything up to 9999.

I did not check the values for month and day, as Excel will accept arguments
that are not in the range of 1-12; 1-31 -- merely doing the appropriate math to
change them to a valid date. For example:

DATE(2000,-3,0) --> 31 AUG 1999



--ron

Some minor changes to *properly* return the #VALUE! error for an invalid date:

================================
Option Explicit

Function GetDt(rg As Range)
Dim sFormula As String
Dim oRegex As Object
Dim oMatchCollection As Object
Dim YMD(1 To 3) As Integer
Dim i As Long
Dim Temp As Variant

sFormula = rg.Formula

Set oRegex = CreateObject("VBScript.RegExp")
oRegex.Pattern = "(\(DATE\()(.*?),(.*?),(.*?)\)"

Set oMatchCollection = oRegex.Execute(rg.Formula)

For i = 1 To 3
Temp = oMatchCollection(0).submatches(i)

If IsNumeric(Temp) Then
YMD(i) = Temp
Else
YMD(i) = Range(Temp).Value
End If
Next i

'Sanity Check

If YMD(1) < 1901 Or YMD(1) > 2200 Then
GetDt = CVErr(xlErrValue)
Exit Function
End If

GetDt = DateSerial(YMD(1), YMD(2), YMD(3))

End Function
=================================
--ron