FInding a Date with wildcard
 

I am trying to find a date within a row of dates. The dates are actually
calculations(a series), formatted to read as dates. The other rub here is
that becasue of the calculation the day of the month is varied since the
original creator of the spreadsheet adds 32 to the previous date. (Oh by the
way the date is formatted for Canada and not the USA which means the month
and day are reversed)

So can I use * or must I use a function to convert the date calculations
beofre I do a Find

I typically use:
ColRef2 = xlApp.Rows(5).Find("1/1/2005").Column

But it is not working since the day value is not "1" but rather a number
other than "1". SO I tried the following without any success
ColRef2 = xlApp.Rows(5).Find("1/*/2005").Column

FInding a Date with wildcard
 

Try as UDF:
1. you'll need to call IsWithin with the value in each cell
2. see the comments in IsWithin
3. your wildcard is BOTH the day of the month & the month of the year
4. this is for the UK: adapt for other regional settings

Function IsWithin(ByVal mydate As Date, ByVal myyear As Integer) As Boolean
Select Case Sgn((DateValue(("01/01/" & myyear)) - mydate)) +
Sgn((DateValue(("31/12/" & myyear)) - mydate))
Case -2
IsWithin = False 'exceeds maximum
Case -1
IsWithin = True 'equals maximum
Case 0
IsWithin = True 'within minimum & maximum
Case 1
IsWithin = True 'equals minimum
Case 2
IsWithin = False ' below mimimum
End Select
End Function

Sub aa()
MsgBox IsWithin("01/03/2004", 2004)
End Sub


"Jenny" wrote:

I am trying to find a date within a row of dates. The dates are actually
calculations(a series), formatted to read as dates. The other rub here is
that becasue of the calculation the day of the month is varied since the
original creator of the spreadsheet adds 32 to the previous date. (Oh by the
way the date is formatted for Canada and not the USA which means the month
and day are reversed)

So can I use * or must I use a function to convert the date calculations
beofre I do a Find

I typically use:
ColRef2 = xlApp.Rows(5).Find("1/1/2005").Column

But it is not working since the day value is not "1" but rather a number
other than "1". SO I tried the following without any success
ColRef2 = xlApp.Rows(5).Find("1/*/2005").Column