How to determine the value?
in [General]
|
Prev: Find certain "Text" in a specific coloum, add corres. values
Next: Help! how to cross - data with excel??
From: Eric on 6 Jun 2010 01:17 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
From: stanleydgromjr on 6 Jun 2010 08:33 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
From: joel on 6 Jun 2010 08:54
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 |