In Excel if is more than 25% from start date then make cell red

[adrian]

Please help me with a formula, or conditional formatting.

i have the situation: a1 = start date, a2 = due date, and 4 cells with
percentage from done (25%, 50%, 75%, 100%)
i want to do: if the percentage from done is not realised in 25% from start
date then make red that cell. then do the same for 50%, 75%...

if i wasn't clear enought please tell me.

thanks.

adrian.

 

[Bernard Liengme]

Are we assume you have something like
start-date due-date 25%-date 50%-date 75%-date
100%-date
1-Jan-2009 1-Jul-2009 20-Feb-2009 1-Apr-2009 16-May-2009 1-Jul-2009
with labels in row 1 and dates in row 2

Since the 25% date (C2) is late, it should be red
The formula for the Conditional Format dialog is =C2>A2+(B2-A2)/4
The rest are similar
=D2>A2+(B2-A2)/2
=E2>A2+(B2-A2)*(3/4)
=F2>A2+(B2-A2) or >B2
best wishes

 

[adrian]

Thank you but it does'nt work.
in cell 25% (C2) i don't have inserted the date, it's only number 25%.
anouther suggestions please?

 

[JLatham]

I believe it will work if you put actual dates into the conditionally
formatted cells. Otherwise I don't see how to accomplish it with just 6
cells, since you have to know WHEN you hit the 25%,50%,75% and 100% marks to
determine if they were late or not.
Another way to do it would be to use 10 cells (plus the labels). Like this:
A1:F1 are the labels: Start Due 25%-due 50%-due 75%-due 100%-due
then in A2 has start date, B2 has final due date.
C2 has formula =A2+((B2-A2)*.25)
D2 has formula =A2+((B2-A2)*.5)
E2 has formula =A2+((B2-A2)*.75)
F2 has formula =B2
Those give you your 'target' dates. Your actual completed dates for each
point would go into row 3 at C3, D3, E3 and F2. Conditional formatting for
each is simply whether or not the entry is greater than the cell above it.
That is, for C2 it would be Value "is greater than" with C2 as the final
entry, and set cell format for red.