Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Tracking Dates For Future Occurrences
Can this be done? I want to track a yearly review. I would like the date,
once entered - say 6/1/2009, to conditionally format to change yellow 30 days before, then red 15 days before, and then to stay red until the date is updated again for say 6/1/2010. Can this be done? I am new to all this, thanks.. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Tracking Dates For Future Occurrences
In 2007 Also..
"Knee2no" wrote: Can this be done? I want to track a yearly review. I would like the date, once entered - say 6/1/2009, to conditionally format to change yellow 30 days before, then red 15 days before, and then to stay red until the date is updated again for say 6/1/2010. Can this be done? I am new to all this, thanks.. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Tracking Dates For Future Occurrences
This is just air code to get you started.
This formula returns the current date. For example, 8/13/2008 is serial number 39673. =DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())) This formula returns the serial number for June 1st of the current year (i.e. it *should* auto-adjust for 2009) =DATEVALUE("6/1/" & YEAR(NOW())) This formula returns 39600 for 6/1/2008. So if you subtract the second number from the first, you get the difference in days (negative numbers means the number of days past 6/1 of the current year). Now you can simply use them in the conditional formatting box to set up your cell coloring. The formula that checks 30 days out should come first, followed by 15 days out, etc... HTH, JP On Aug 13, 4:10*pm, Knee2no wrote: In 2007 Also.. "Knee2no" wrote: Can this be done? *I want to track a yearly review. *I would like the date, once entered - say 6/1/2009, to conditionally format to change yellow 30 days before, then red 15 days before, and then to stay red until the date is updated again for say 6/1/2010. *Can this be done? *I am new to all this, thanks..- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Tracking Dates For Future Occurrences
This is a great start, but I am confused on setting up the formula's in the
conditional formatting. Sorry, I have nearly no experience.. "JP" wrote: This is just air code to get you started. This formula returns the current date. For example, 8/13/2008 is serial number 39673. =DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())) This formula returns the serial number for June 1st of the current year (i.e. it *should* auto-adjust for 2009) =DATEVALUE("6/1/" & YEAR(NOW())) This formula returns 39600 for 6/1/2008. So if you subtract the second number from the first, you get the difference in days (negative numbers means the number of days past 6/1 of the current year). Now you can simply use them in the conditional formatting box to set up your cell coloring. The formula that checks 30 days out should come first, followed by 15 days out, etc... HTH, JP On Aug 13, 4:10 pm, Knee2no wrote: In 2007 Also.. "Knee2no" wrote: Can this be done? I want to track a yearly review. I would like the date, once entered - say 6/1/2009, to conditionally format to change yellow 30 days before, then red 15 days before, and then to stay red until the date is updated again for say 6/1/2010. Can this be done? I am new to all this, thanks..- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Tracking Dates For Future Occurrences
Sorry I may have led you in slightly the wrong direction. You wanted
to enter a date in a cell and check how far it is from June 1st of whatever year it happens to be. First I went to the name box (Ctrl-F3) and created a named constant "DateToChecK' and in the "Refers to" box I entered: =DATEVALUE("6/1/" & YEAR(NOW())) Then I selected cell H2 and entered the following formula in the conditional formatting box: =IF((H2-DateToCheck)<=30,TRUE,FALSE) Then set a pattern of Yellow background for the cell. Then I added a second condition by clicking the "Add " button and entered this formula: =IF((H2-DateToCheck)<=15,TRUE,FALSE) For that condition I set a pattern of Red background for the cell. Keep in mind this is for Excel 2003. Apologies if it doesn't work in 2007. Here are some great samples: http://www.cpearson.com/excel/cformatting.htm http://www.contextures.com/xlCondFormat02.html Then you can test it out by entering dates in the current year that are before 6/1 (in this case, 2008). When you get 30 days out, the cell turns yellow. When you get 15 days out, it turns red. Once you get past the date, of course, it should change back to normal. HTH, JP On Aug 13, 10:51*pm, Knee2no wrote: This is a great start, but I am confused on setting up the formula's in the conditional formatting. *Sorry, I have nearly no experience.. "JP" wrote: This is just air code to get you started. This formula returns the current date. For example, 8/13/2008 is serial number 39673. =DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())) This formula returns the serial number for June 1st of the current year (i.e. it *should* auto-adjust for 2009) =DATEVALUE("6/1/" & YEAR(NOW())) This formula returns 39600 for 6/1/2008. So if you subtract the second number from the first, you get the difference in days (negative numbers means the number of days past 6/1 of the current year). Now you can simply use them in the conditional formatting box to set up your cell coloring. The formula that checks 30 days out should come first, followed by 15 days out, etc... HTH, JP On Aug 13, 4:10 pm, Knee2no wrote: In 2007 Also.. "Knee2no" wrote: Can this be done? *I want to track a yearly review. *I would like the date, once entered - say 6/1/2009, to conditionally format to change yellow 30 days before, then red 15 days before, and then to stay red until the date is updated again for say 6/1/2010. *Can this be done? *I am new to all this, thanks..- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding # of occurrences of information between two dates | Excel Worksheet Functions | |||
Tracking the Trend of Occurrences | Excel Discussion (Misc queries) | |||
future dates | Excel Worksheet Functions | |||
future dates | Excel Worksheet Functions | |||
Formatting dates in the future | Excel Worksheet Functions |