How to determine the value?

[Eric]

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
[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

 

[stanleydgromjr]

Eric,

Please attach your workbook.

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

 

[joel]

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