Does anyone have any suggestions on how to determine the value within lists?
For example,
[1]
There is a given date in cell A1, 06-Jun without concerning year, I would 
like to determine the value within column C, it should return 6 in cell A2, 
because 06-Jun is equal to or larger than 05-Jun and less than 21-Jun.
[2]
There is a given date in cell A1, 05-Jun without concerning year, I would 
like to determine the value within column C, it should return 6 in cell A2, 
because 05-Jun is equal to or larger than 05-Jun and less than 21-Jun.
[3]
There is a given date in cell A1, 07-Mar without concerning year, I would 
like to determine the value within column C, it should return 24 in cell A2, 
because 07-Mar is equal to or larger than 05-Mar and less than 21-Mar.

Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

There are list under columns
[B]          [C]
21-Mar	1 
05-Apr	2 
20-Apr	3 
05-May	4 
21-May	5 
05-Jun	6 
21-Jun	7 
07-Jul	8 
23-Jul	9 
07-Aug	10 
23-Aug	11 
07-Sep	12 
22-Sep	13 
08-Oct	14 
23-Oct	15 
08-Nov	16 
22-Nov	17 
07-Dec	18 
21-Dec	19 
05-Jan	20 
20-Jan	21 
04-Feb	22 
19-Feb	23 
05-Mar	24 

Eric,

Please attach your workbook.

Click on the *Reply* button, then scroll down and see *Manage
Attachments*.


-- 
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: http://www.thecodecage.com/forumz/member.php?u=503
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=207432

http://www.thecodecage.com/forumz

This gets complicated because yo uprobably have a date with the year
hidden since you are only displaying month and day.  The function has to
ignore the year which may or may not be entered correctly.  I think you
need a UDF macro.  I wrote one that I tried to make idiot proff no
matter what a person entered as the year on the worksheet.  Put this
function in A2

=ReturnDateIndex(A1,$B$1:$C$24)


The macro is below

Function ReturnDateIndex(LookupDateStr As String, Target As Range)

'convert date to a serial date if it is not already
LookupDate = DateValue(LookupDateStr)

StartRow = Target.Row
NumberRows = Target.Rows.Count
LastRow = StartRow + NumberRows - 1

ReturnDateIndex = "VALUE"
FoundNewYear = False
'assume dates are in order
'if a day is earlier in the year than previous
'date then a new year must of been found
StartYear = Year(LookupDate)
StartMonth = Month(LookupDate)
StartDay = Day(LookupDate)

PreviousDate = DateValue(Cells(StartRow, Target.Column))
'convert date to same year as lookup date
PreviousDate = DateSerial(StartYear, _
Month(PreviousDate), _
Day(PreviousDate))


For RowCount = StartRow To LastRow
CellDate = DateValue(Cells(RowCount, Target.Column))
'convert date to same year as lookup date
CellDate = DateSerial(StartYear, _
Month(CellDate), _
Day(CellDate))

'if true then we reached a new year
If CellDate < PreviousDate And _
FoundNewYear = False Then

FoundNewYear = True
'move date to next year
LookupDate = DateSerial(StartYear + 1, _
Month(LookupDate), _
Day(LookupDate))
End If

If FoundNewYear = True Then
'move date to next year
CellDate = DateSerial(StartYear + 1, _
Month(CellDate), _
Day(CellDate))
End If

If RowCount = LastRow Then
If LookupDate >= CellDate Then
ReturnDateIndex = Cells(RowCount, Target.Column + 1)
End If
Else
NextDate = DateValue(Cells(RowCount + 1, Target.Column))
'convert date to same year as lookup date

If FoundNewYear = True Then
'move date to next year
NextDate = DateSerial(StartYear + 1, _
Month(NextDate), _
Day(NextDate))
Else
NextDate = DateSerial(StartYear, _
Month(NextDate), _
Day(NextDate))

End If

If LookupDate >= CellDate And _
LookupDate <= NextDate Then
ReturnDateIndex = Cells(RowCount, Target.Column + 1)
Exit For
End If

End If

Next RowCount

End Function


-- 
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=207432

http://www.thecodecage.com/forumz