formula / code help needed
 

Hi
I have a sheet with column C (start time) & column D (end time). (C1:D50)
e.g

C D
07:45 07:50
07:48 08:05

In Cell A1 i have the time '08:00' and in cell A2 '08:20'.

What i want to do is look at the range C1:D50 and determine if any values
fall between 08:00 and 08:20. If they do, change the corrosponding cell in
column E to equal 10.


C D E
07:45 07:50
07:48 08:05 10

Some sort of formula or vb code along the lines of If any cell in Range
C1:D50 is between 08:00 and 08;20 then in that row the cell in column E
becomes 10

can anyone help?

Thanks in advance
Paul Watkins

paste this formula into cell E1 and copy it down

=IF(OR(AND($A$1<D1,D1<$B$1),AND($A$1<C1,C1<$B$1)), 10,0)


"Paul Watkins" wrote:

Hi
I have a sheet with column C (start time) & column D (end time). (C1:D50)
e.g

C D
07:45 07:50
07:48 08:05

In Cell A1 i have the time '08:00' and in cell A2 '08:20'.

What i want to do is look at the range C1:D50 and determine if any values
fall between 08:00 and 08:20. If they do, change the corrosponding cell in
column E to equal 10.


C D E
07:45 07:50
07:48 08:05 10

Some sort of formula or vb code along the lines of If any cell in Range
C1:D50 is between 08:00 and 08;20 then in that row the cell in column E
becomes 10

can anyone help?

Thanks in advance
Paul Watkins

Thanks, that works perfectly.

Paul


"Duke Carey" wrote in message
...
paste this formula into cell E1 and copy it down

=IF(OR(AND($A$1<D1,D1<$B$1),AND($A$1<C1,C1<$B$1)), 10,0)


"Paul Watkins" wrote:

Hi
I have a sheet with column C (start time) & column D (end time). (C1:D50)
e.g

C D
07:45 07:50
07:48 08:05

In Cell A1 i have the time '08:00' and in cell A2 '08:20'.

What i want to do is look at the range C1:D50 and determine if any values
fall between 08:00 and 08:20. If they do, change the corrosponding cell
in
column E to equal 10.


C D E
07:45 07:50
07:48 08:05 10

Some sort of formula or vb code along the lines of If any cell in Range
C1:D50 is between 08:00 and 08;20 then in that row the cell in column E
becomes 10

can anyone help?

Thanks in advance
Paul Watkins