CF Puzzle

[mole999]

R1 = NOW()

column Kn and Ln formated as dd/mm/yyyy

premise to colour Kn

If Kn is blank, no format

If Kn > = R1 AND Ln = "" Then RED (Kn)

If Kn Between -14 and -8 and Ln = "" Then GREEN (Kn)

If Kn Between -7 and -1 and Ln = "" Then AMBER (Kn)

I could get 2003 to work, stil trying to get to grips with 2007


quick kick would be appreciated

 

[Dryver14]

I have not really looked too hard at the formatting but isnt NOW() too
precise, maybe =TODAY() would be better

 

[mole999]

yes, not a problem really for developing, just trying to get my head around the logic, have had to develop a helper cell to trigger the formatting as i cannot figure how to apply directly in the CF, today was what I used on the helper

 

[shg]

Why is K is formatted as d/m/y when it contains numbers that aren't dates, e.g., -14.

Three formula rules:

=(K1 >= $R$1)*(L1 = "") format red, highest priority, stop if true

=(K1>=-14)*(K1<=-7)*(L1="") form green, stop is true

=(K1 > -7) * (K1 <= -1) * (L1=""), lowest priority, stop if true

 

[mole999]

column K and L do both contain dates.

isn't a date just a whole number, regardless of how displayed

thank you for showing the formula rules, as I couldn't get anything close to usable to work

 

[shg]

I'm confused: If K can have negative values, as the CF is based on, it can't be a date.

 

[mole999]

i was looking for
due in 14 days,
due in 7 days
overdue,

and yes I do see your point about negative dates, didn't strike me until you said it

elsewhere pointed me at Today() -14

so ended up on a helper cell with

=IF(K2="",0,IF(AND(ISBLANK(L2),K2<=TODAY()),1,IF(AND(ISBLANK(L2),K2<(TODAY()+7)),2,IF(AND(ISBLANK(L2),K2<(TODAY()+14)),3,0))))

 

[shg]

All sorted now?

 

[mole999]

acceptable, though I really wanted it to be just in the CF, and not need the helper cells

 

[shg]

It can be done without helper cells.

What's in col K (or is that the helper column?), what's in col L, and what's in R1?