Relate a date to a date range

[troy_lee]

On worksheet B I have a cell with the formula:

TODAY()

.

On worksheet A I have a table with lots of different calendar information. In column B of this sheet is the work week beginning date, always a Monday. In column C is the work week ending date, always a Friday. I want to take the current date from worksheet B and return the row number in Worksheet A where today's date falls in between the week dates in Column B and C.

Thanks in advance for the help.

TL

 

[Domski]

Try something like:

=MATCH(A1,'Worksheet A'!$B$2:$B$30,1)

Dom

 

[troy_lee]

Doesn't quite work with what I've got. I looked for this date as a test, 3/4/10. This date falls between 3/1/10 (Column B) and 3/5/10 (Column C) on my sheet. Excel returned a row reference a few rows before this one. Should I change the dates to a long integer first and then perform the Match?

 

[Domski]

The match formula returns the index number of the item relative to it's position in the array of data, hence if your data starts in row 2 and it the match it finds is in row 5 the index that it will return will be 4. Do you mean that you require the actual sheet row number? Can I ask what you are planning to use this for?

Dom

 

[troy_lee]

Gotcha. Thanks. I think that works for me.