Between dates table lookup.

[news]

Hello,


Ok I have the following table:

In column A there are a list of dates - the start date of the week.
In columns B there is another list of dates - the end date of the
week.
In column C there is a code.

For example:


A B C
06/12/04 12/12/04 1234
13/12/04 19/12/04 5678
2012/04 26/12/04 9101


and so on.


I would either like to create a forumula for a cell, or some VB code
that I could attach to a command button that would get the current
date, then check to see if it falls on or between the dates in column
A & B, then return the correpsonding code from C.

e.g.

If today's date is 14/12/04, the formula or code would look in column
A & B, find that it falls beween the dates 13/12/04 & 19/12/04, then
return the code "5678."

I'm not sure how to go about this... any help appreciated!

thanks


Michael

 

[Frank Kabel]

Hi
formula solution:
=VLOOKUP(TODAY(),A1:C100,3,TRUE)

Note: you have to sort column A scending

 

[Bob Phillips]

formula version

=SUMPRODUCT(((A1:A10<=TODAY())*(B1:B10>=TODAY())=1)*(C1:C10))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Here is a code example if u choose to attach to a button. the answer will
show in a message unless u modify the code to place it in a cell.

Sub Macro1()
Dim now As Date
Dim msg As TextEffectFormat
Range("a1").Select
now = Range("e1").Value
Do Until ActiveCell = ""
If ActiveCell <= now And ActiveCell.Offset(0, 1) >= now Then
ans = ActiveCell.Offset(0, 2).Value
MsgBox (ans)
Exit Do
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop

End Sub

HTH
Devin

 

[Guest]

I missread part of the question - here is the code correction

Sub Macro1()
Dim now As Date
Range("a1").Select
now = Date
Do Until ActiveCell = ""
If ActiveCell <= now And ActiveCell.Offset(0, 1) >= now Then
ans = ActiveCell.Offset(0, 2).Value
MsgBox (ans)
Exit Do
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop

End Sub

 

[news]

Thanks guys for your help. All solutions worked, but in the end I
settled for using this formula

=IF(OR(TODAY()<MIN(A:A),TODAY()>MAX(B:B)),"Date out of
range",VLOOKUP(TODAY(),A:C,3,TRUE))


regards

Michael