formula / code help needed

[Paul Watkins]

Hi
I have a sheet with column C (start time) & column D (end time). (C150)
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 C150 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
C150 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

 

[Guest]

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]

Thanks, that works perfectly.

Paul

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)