Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting adding different cell dates
With help from members, I have the following formula and have added 3 colours
to the conditional formatting section, being red for the top, orange for the middle and lastly yellow for the bottom one. Basically, any red cells indicate persons who are out of date and need urgent training (in my case-First Aid but could be used for other training purposes), the orange indicates an orange cell which implies to me, 'Get my skates on and get something organised quickly' and the yellow indicates 'Hello, I need to be considered for training soon' =AND(G3<,DATE(YEAR(G3)+1,MONTH(G3),DAY(G3)) <=TODAY()) =DATEDIF(G3,TODAY(),m)=11 =DATEDIF(G3,TODAY(),m)=10 I currently have 3 columns with the above conditions applying; D3 to D48 K3 to K48 P3 to P30 I would like all those cells in the 3 columns to add up and show different statistics. For instance In Date: Any conditional formatting cells coloured white, orange and yellow cells, but does not include those out of date which is the pink cells. These are to add up and placed in cell P35 Out of Date: Any conditional formatting cells coloured red are classed as out of date (as per line one) and the 3 columns with this formula add together to make a total in cell P36 Thanks to those who've helped so far and thanks to those in advance. ----------------------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting adding different cell dates
Analytics based on conditional formatting is tricky. This should give you
some ideas about what to do: http://www.cpearson.com/excel/CFColors.htm HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Manxy" wrote: With help from members, I have the following formula and have added 3 colours to the conditional formatting section, being red for the top, orange for the middle and lastly yellow for the bottom one. Basically, any red cells indicate persons who are out of date and need urgent training (in my case-First Aid but could be used for other training purposes), the orange indicates an orange cell which implies to me, 'Get my skates on and get something organised quickly' and the yellow indicates 'Hello, I need to be considered for training soon' =AND(G3<,DATE(YEAR(G3)+1,MONTH(G3),DAY(G3)) <=TODAY()) =DATEDIF(G3,TODAY(),m)=11 =DATEDIF(G3,TODAY(),m)=10 I currently have 3 columns with the above conditions applying; D3 to D48 K3 to K48 P3 to P30 I would like all those cells in the 3 columns to add up and show different statistics. For instance In Date: Any conditional formatting cells coloured white, orange and yellow cells, but does not include those out of date which is the pink cells. These are to add up and placed in cell P35 Out of Date: Any conditional formatting cells coloured red are classed as out of date (as per line one) and the 3 columns with this formula add together to make a total in cell P36 Thanks to those who've helped so far and thanks to those in advance. ----------------------------------------------------------------------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting adding different cell dates
Thanks Ryan for the information but to be honest, I didn't understand it as I
struggle with adding lines up! It might be easier if the formula just kept the one rule; Out of Date: Any conditional formatting cells coloured red are classed as out of date (as per cond. form. in line one) and the 3 columns with this formula add together to make a total in cell P36 So basically, when the cell turns red, it adds to the total out of date in cell P36 ------------------------------- "ryguy7272" wrote: Analytics based on conditional formatting is tricky. This should give you some ideas about what to do: http://www.cpearson.com/excel/CFColors.htm HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Manxy" wrote: With help from members, I have the following formula and have added 3 colours to the conditional formatting section, being red for the top, orange for the middle and lastly yellow for the bottom one. Basically, any red cells indicate persons who are out of date and need urgent training (in my case-First Aid but could be used for other training purposes), the orange indicates an orange cell which implies to me, 'Get my skates on and get something organised quickly' and the yellow indicates 'Hello, I need to be considered for training soon' =AND(G3<,DATE(YEAR(G3)+1,MONTH(G3),DAY(G3)) <=TODAY()) =DATEDIF(G3,TODAY(),m)=11 =DATEDIF(G3,TODAY(),m)=10 I currently have 3 columns with the above conditions applying; D3 to D48 K3 to K48 P3 to P30 I would like all those cells in the 3 columns to add up and show different statistics. For instance In Date: Any conditional formatting cells coloured white, orange and yellow cells, but does not include those out of date which is the pink cells. These are to add up and placed in cell P35 Out of Date: Any conditional formatting cells coloured red are classed as out of date (as per line one) and the 3 columns with this formula add together to make a total in cell P36 Thanks to those who've helped so far and thanks to those in advance. ----------------------------------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help adding a formula to to cell with conditional formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting: Adding more then 3 | Excel Worksheet Functions | |||
Conditional Formatting- adding more than 3 conditions | Excel Discussion (Misc queries) | |||
Adding/Subtracting Dates and Conditional Formatting | Excel Programming | |||
How do I highlight a cell using conditional formatting and dates | Excel Worksheet Functions |