If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
In Excel if is more than 25% from start date then make cell red
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. |
#2
|
|||
|
|||
In Excel if is more than 25% from start date then make cell red
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 =C2A2+(B2-A2)/4 The rest are similar =D2A2+(B2-A2)/2 =E2A2+(B2-A2)*(3/4) =F2A2+(B2-A2) or B2 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "adrian" wrote in message ... 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. |
#3
|
|||
|
|||
In Excel if is more than 25% from start date then make cell re
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? "Bernard Liengme" wrote: 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 =C2A2+(B2-A2)/4 The rest are similar =D2A2+(B2-A2)/2 =E2A2+(B2-A2)*(3/4) =F2A2+(B2-A2) or B2 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "adrian" wrote in message ... 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. |
#4
|
|||
|
|||
In Excel if is more than 25% from start date then make cell re
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. "adrian" wrote: 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? "Bernard Liengme" wrote: 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 =C2A2+(B2-A2)/4 The rest are similar =D2A2+(B2-A2)/2 =E2A2+(B2-A2)*(3/4) =F2A2+(B2-A2) or B2 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "adrian" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|