Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A better explanation of help required
I have to assess my team of 70 every six months so if the 1st assessment is
01.01.06 then the next is due on the 01.12.06. I now how to show the next date due by taking the cell +180 to give me the next assessment date. What I want to do is get a reminder 120 days (Coloured Green) from the original assessment date and then a final reminder at 160 days(Coloured Red ) so I do not miss their assessments I have tried to use conditional formating but I do not know how to get it to work as each original assessment dates are different depending when they joined the team. How do I get Excel to calculate "X" amount of days forward and change colour to give me a visual reminder without trawling through the spreadsheet. Column E Column F Original Dates Dates in six months time 19 May 2006 15 November 2006 26 August 2005 22 February 2006 27 August 2005 23 February 2006 28 August 2005 24 February 2006 29 August 2005 25 February 2006 30 August 2005 26 February 2006 Any help will be most appreciated Elvey |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A better explanation of help required
Hi again!
One work around is to set up a third column to calculate the days left before the assessment date. You may use the ff formula =DATEDIF(TODAY(),assessment date,"d") please note that this function is valid only until the assessment date then based on the results you may now apply conditional formatting using two conditions. Condition 1Cell Value Is... Less than or equal to 120 apply the desired format (green) Condition 2Cell value Is... Between 121 and 160 apply the desired format (red) These conditional formatting may be applied either to the DATEDIF results cells or to another column(?) before the original dates containing the employee name. Hope this helps! -- Thanks and kind regards "Elvey" wrote: I have to assess my team of 70 every six months so if the 1st assessment is 01.01.06 then the next is due on the 01.12.06. I now how to show the next date due by taking the cell +180 to give me the next assessment date. What I want to do is get a reminder 120 days (Coloured Green) from the original assessment date and then a final reminder at 160 days(Coloured Red ) so I do not miss their assessments I have tried to use conditional formating but I do not know how to get it to work as each original assessment dates are different depending when they joined the team. How do I get Excel to calculate "X" amount of days forward and change colour to give me a visual reminder without trawling through the spreadsheet. Column E Column F Original Dates Dates in six months time 19 May 2006 15 November 2006 26 August 2005 22 February 2006 27 August 2005 23 February 2006 28 August 2005 24 February 2006 29 August 2005 25 February 2006 30 August 2005 26 February 2006 Any help will be most appreciated Elvey |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
A better explanation of help required
Modification to my earlier post (after reading again your explanation)
formula should be... =DATEDIF(original date,today(),"d") Conditional formating still the same. -- Thanks and kind regards "RaymundCG" wrote: Hi again! One work around is to set up a third column to calculate the days left before the assessment date. You may use the ff formula =DATEDIF(TODAY(),assessment date,"d") please note that this function is valid only until the assessment date then based on the results you may now apply conditional formatting using two conditions. Condition 1Cell Value Is... Less than or equal to 120 apply the desired format (green) Condition 2Cell value Is... Between 121 and 160 apply the desired format (red) These conditional formatting may be applied either to the DATEDIF results cells or to another column(?) before the original dates containing the employee name. Hope this helps! -- Thanks and kind regards "Elvey" wrote: I have to assess my team of 70 every six months so if the 1st assessment is 01.01.06 then the next is due on the 01.12.06. I now how to show the next date due by taking the cell +180 to give me the next assessment date. What I want to do is get a reminder 120 days (Coloured Green) from the original assessment date and then a final reminder at 160 days(Coloured Red ) so I do not miss their assessments I have tried to use conditional formating but I do not know how to get it to work as each original assessment dates are different depending when they joined the team. How do I get Excel to calculate "X" amount of days forward and change colour to give me a visual reminder without trawling through the spreadsheet. Column E Column F Original Dates Dates in six months time 19 May 2006 15 November 2006 26 August 2005 22 February 2006 27 August 2005 23 February 2006 28 August 2005 24 February 2006 29 August 2005 25 February 2006 30 August 2005 26 February 2006 Any help will be most appreciated Elvey |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
A better explanation of help required
highlight the cells you want to conditional format select conditional format select, if cell value is greater than or equal to, then select the cells you want to compare to then select the condition then click add and do it again but this time select less than, and select the condition you want then press ok the one cell should have the formula =today() -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=543972 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Required fields in Excel | New Users to Excel | |||
can you make a cell value required? | Excel Discussion (Misc queries) | |||
highlighting required fields | Excel Worksheet Functions | |||
SPECIFY REQUIRED PAPER SIZE IN EXCEL 2000 | Excel Discussion (Misc queries) | |||
Rate of return required formula | Excel Worksheet Functions |