formula to track timeframe by automatically highlighting cells

[Guest]

Hello,

I set up an excel sheet to have dates running in row 1. Example: K1 =
1/1/07, L1 = 1/15/07, M1 = 1/29/07, N1 = 2/12/07, you notice the dates run
every 2 week intervals. This goes all the way to BP1 = 3/9/09.

Cell H2 = start date
Cell I2 = end date

Now, I need to set a formula where if user enters start date and end date in
H2 and I2 then cells K2 - BP2 will highlight "yellow" accordingly. This is
to make easy visually to track and manage projects.

Also, I need to have 2 more conditional formulas to highlight cells.

1) This formula will take the start date and calculate backwards 45 days to
highlight pre-work (green)
2) This formula will take the end date and tack on 14 days for post work and
highlight these cells red.

Can this be done? Thanks for looking.

 

[Guest]

If I'm understanding you correctly, these conditional formatting formulas
should work. Put these in K2 and then copy the format out to BP2 and down to
however many rows of start and finish dates you'll have.

For green: "=AND($I2+14>=K$1,$I2+14<=L$1)"
For red: "=AND($H2-45>=K$1,$H2-45<=L$1)"
For Yellow: "=AND($H2<=L$1,$I2>=K$1)"

Will

 

[Guest]

Thanks Roadkill,

Your response worked but not completely in the way as expected. I switched
it around alittle to make the colors display in the way my boss wants it.
This is what I have now but it still has a faulty area.

red =AND($I2+14>=U$1,$I2+14<=V$1)
yellow =AND($H2-45>=U$1,$H2-45<=V$1)
green =AND($H2<=V$1,$I2>=U$1)

The only problem I have with this one is that the yellow does not highlight
the whole 45 days for prework. It only highlights the first cell in which
the prework starts.

Do you know what I can do here? Your answer was really helpful in my
discovery.

 

[Guest]

Try changing yellow to "=AND($H2-45<=V$1,$I2-45>=U$1)" AND MAKE SURE THAT THE
GREEN CONDITION IS BEFORE THE YELLOW.
Will

 

[Guest]

Roadkill you're a genius. For real.

I have one more request just brought onto me. And let me know if this is
possible to do. Is there a way to incorporate 2 more dates:

Tentitive Start Date = F1 and
Tentitive End Date = G1

so if there are no confirmed Start date (H1) and End date (I1) then we can
use the tentitive dates to track projects. But if there are Confirmed Start
and End date then we use those. It's confusing I know.

In any way Roadkill, you've been awesome. Thanks so much.

 

[Guest]

I assume you intended to say that the tentative dates are in F2 and G2? If
so, try (in U2):

Red:
"=IF(OR(LEN($I2)=0,LEN($H2)=0),AND($G2+14>=U$1,$G2+14<=V$1),AND($I2+14>=U$1,$I2+14<=V$1))"

Green:
"=IF(OR(LEN($H2)=0,LEN($I2)=0),AND($F2<=V$1,$G2>=U$1),AND($H2<=V$1,$I2>=U$1))"

Yellow:
"=IF(OR(LEN($H2)=0,LEN($I2)=0),AND($F2-45<=V$1,$G2-45>=U$1),AND($H2-45<=V$1,$I2-45>=U$1))"

This requires that both start and finish date be present or it uses the
tentative dates. Also, it just checks for something to be in start and
finish, not necessarily a date. So if there is even a space in the start and
finish it will try and use them (so make sure they are either empty or have
dates in them).

Will

 

[Guest]

Roadkill,

Yesssss! I don't know how you do it but you did it! Thank you so much. I
hope one day I can be at your level and help others too.