Is Year in YearString

[AlexanderBB]

Just wondering th best way to see if string "1965" is within string "1944-1983".
Would it be better to convert to Date variables ?

 

[Rick Rothstein]

Just wondering th best way to see if string "1965" is within string "1944-1983".
Would it be better to convert to Date variables ?

If you are willing to consider a UDF (user defined function)...

Function IsYearIn(TheYear As Variant, TheRange As Variant) As Boolean
  Dim Parts() As String
  Parts = Split(TheRange, "-")
  IsYearIn = Parts(0) <= TheYear And TheYear <= Parts(1)
End Function

 

[Rick Rothstein]

If you are willing to consider a UDF (user defined function)...

Function IsYearIn(TheYear As Variant, TheRange As Variant) As Boolean
  Dim Parts() As String
  Parts = Split(TheRange, "-")
  IsYearIn = Parts(0) <= TheYear And TheYear <= Parts(1)
End Function

And here is an Excel formula solution (assuming cell A1 contains the year being tested and cell A2 contains the date range text string)...

=AND((0+LEFT(A2,FIND("-",A2)-1))<=A1,A1<=(0+MID(A2,FIND("-",A2)+1,4)))

 

[AlexanderBB]

Thanks very much Rick, it's doing the job perfectly. I wondered about a Date variable
being 'more correct' but I'm only using YYYY and the various other date parameters may be overkill.

 

[Phuoc]

One way:

=SUMPRODUCT(--(ROW(INDIRECT(SUBSTITUTE(A2,"-",":")))=A1))>0

 

[AlexanderBB]

There's no way to use a worksheet formula in VBA is there ?

 

[Rick Rothstein]

There's no way to use a worksheet formula in VBA is there ?

With some limitations you can... what is the formula that you have in mind?

 

[Scott Huish]

To a certain degree, yes, but to what end?

 

[AlexanderBB]

Nothing specific Rick, just to try out/test those from you and Phuoc above. Others turn up from time to time too.

 

[Rick Rothstein]

Nothing specific Rick, just to try out/test those from you and Phuoc above. Others turn up from time to time too.

As I indicated, it depends on the formula you want to implement... there is no "general" method that works for all formulas... like any other piece of VB code, the code has to be designed to respond to the exact situation it is meant for.