Need to determine if date of event is between certain dates

[wordperfectconvert]

I have a spreadsheet where the user enters the date of an event. Cell B2 is the date of the event. For example, if B2 is 1/1/07, I need to have cell C2 enter $431.00. If B2 is 1/1/08, I need to have cell C2 enter $461.00.

I literally am struggling with everything. I started with Private Sub Worksheet_SelectionChange(ByVal Target As Range)and am trying to figure out how to set my target (is it Target.Address ("B2")). I understand I am supposed to change the dates to numbers, but can't find the code. Below is the English language version.

If cell B2 is between dates 7/1/06 and 7/1/07, then enter $431, ELSE If cell B2 is between dates 7/1/07 and 7/1/08, then enter $461. I am going to have dates covering 1990 to 2009, so I have to use VBA instead of an excel formula. Thank you in advance for the help.

 

[SkipVought]

hi,

"I am going to have dates covering 1990 to 2009, so I have to use VBA instead of an excel formula."

HUH?! Not so, grasshopper!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ettienne]

Select Case B2
Case 7/1/2006 to 6/30/2007
C2 = 431
Case 7/1/2007 to 6/30/2008
C2 = 461

End select

 

[SkipVought]

Sorry, that is not correct...

Select Case [B2]
  Case #7/1/2006# to #6/30/2007#
     [C2] = 431
  Case #7/1/2007# to #6/30/2008#
     [C2] = 461
End select

But there is no reason in the world that his could be done without VBA...
[tt]
=if(AND(B2>=DateValue("7/1/2006"),B2<=DateValue("6/30/2007")),431,if(AND(B2>=DateValue("7/1/2007"),B2<=DateValue("6/30/2008")),461,""))
[/tt]
However, I would recommend entering the dates into cells and referencing the cells rather than literal values.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ettienne]

I should have added "... below is the English language version...

 

[strongm]

>the English language version

For a particualr subset of the term "English language" ... ;-)

 

[wordperfectconvert]

Thanks to all who contributed. I gave up on attempting to learn the VBA code. It is way over my head. For those looking for the answer, here is my solution.

I set up a separate worksheet named MaxWage that had 4 columns and 20 rows. I then used the following formula in the cell I was using for the answer.

=IF(OR(E8="",E9=""),"",(VLOOKUP(E5,MaxWage!A1:D21,3,TRUE)))

For those concerned about my stubbornness, I was trying to learn something new. I apologize for the frustration.