Check date range on three sheets?

G

Geo

How can I get a date to turn red if it falls within a date range in two other
sheets.
ie:
Leave dates for three person are inserted in their own sheets. If one of
them chooses to have leave dates that conflicts with another, is there a way
if the date to turn red?
 
S

Stefi

Assume that leave start dates are in A2,
end dates in B2,
then select range A2:B2 on sheet of person 1, name this range leave1,
do the same with sheet of person 2, range name leave2,

and apply conditional formatting on date on sheet of person 3 with this
formula:

=OR(AND(A2>=INDEX(leave1,1,1),A2<=INDEX(leave1,1,2)),AND(A2>=INDEX(leave2,1,1),A2<=INDEX(leave2,1,2)))


Regards,
Stefi

„Geo†ezt írta:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date range within a date range 2
Conditional format to change row colour 1
Appointments, Perpetual Calender 2
Copy Sheet to new Sheet and clear cells on original sheets 7
Help with Conditional Formatting on Anniversary Date 2
how do i get a date to turn red if it is within 90 days of today? 1
Date between date range 1
Linking sheets which must update the date first Excel 2007 1

Top