It appears that no date intervals will overlap so try this:

A2 = some date

=SUMPRODUCT(--(A2=Sheet1!A2:A5),--(A2<=Sheet1!B2:B5),Sheet1!C2:C5)

=SUMPRODUCT(--(A2=Start),--(A2<=End),Round)

--
Biff
Microsoft Excel MVP


"simer" wrote in message
...
I have two worksheets:

The first sheet contains date ranges that relate to specific "Rounds":

Start End Round
3-Apr-10 15-Apr-10 1
20-Mar-10 2-Apr-10 2
6-Mar-10 19-Mar-10 3
20-Feb-10 5-Mar-10 4

The second worksheet contains a list of tasks which include a start date
and
an end date. I would like to set up a formula that would look at the end
date
for each task, and automatically assign the proper "Round" number in a
separate column, using the data from the first sheet. So for example, if a
task has an end date of 5-Apr-10, the formula will insert a 1 in the Round
column in that specific row.

HELP!!!