Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date Due Reminder
On my spread sheet I have a cell to indicate testing due dates - Is there a
way to make that cell flash or change colors 60 days prior to the current date ? |
#2
|
|||
|
|||
Hi George
to make it change colours you can use Conditional Formatting - select your dates - ensure that the first date is at the top (and visible) of your worksheet choose format /conditional formatting choose formula is type =AND($A2=TODAY()-60,$A2<=TODAY()) where A2 is the first date in your range click on Format choose a formatting - background fill colours are under "pattern" click OK twice -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "George" wrote in message ... On my spread sheet I have a cell to indicate testing due dates - Is there a way to make that cell flash or change colors 60 days prior to the current date ? |
#3
|
|||
|
|||
Thanks - will give it a try
"JulieD" wrote: Hi George to make it change colours you can use Conditional Formatting - select your dates - ensure that the first date is at the top (and visible) of your worksheet choose format /conditional formatting choose formula is type =AND($A2=TODAY()-60,$A2<=TODAY()) where A2 is the first date in your range click on Format choose a formatting - background fill colours are under "pattern" click OK twice -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "George" wrote in message ... On my spread sheet I have a cell to indicate testing due dates - Is there a way to make that cell flash or change colors 60 days prior to the current date ? |
#4
|
|||
|
|||
let us know how you go
"George" wrote in message ... Thanks - will give it a try "JulieD" wrote: Hi George to make it change colours you can use Conditional Formatting - select your dates - ensure that the first date is at the top (and visible) of your worksheet choose format /conditional formatting choose formula is type =AND($A2=TODAY()-60,$A2<=TODAY()) where A2 is the first date in your range click on Format choose a formatting - background fill colours are under "pattern" click OK twice -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "George" wrote in message ... On my spread sheet I have a cell to indicate testing due dates - Is there a way to make that cell flash or change colors 60 days prior to the current date ? |
#5
|
|||
|
|||
Julie - I tried the formula and I can't get it to work. When I type in your
steps the entire column the background changes to Red ( Which I selected ) regardless of the date.My column is E5:E44 Thanks - George "JulieD" wrote: let us know how you go "George" wrote in message ... Thanks - will give it a try "JulieD" wrote: Hi George to make it change colours you can use Conditional Formatting - select your dates - ensure that the first date is at the top (and visible) of your worksheet choose format /conditional formatting choose formula is type =AND($A2=TODAY()-60,$A2<=TODAY()) where A2 is the first date in your range click on Format choose a formatting - background fill colours are under "pattern" click OK twice -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "George" wrote in message ... On my spread sheet I have a cell to indicate testing due dates - Is there a way to make that cell flash or change colors 60 days prior to the current date ? |
#6
|
|||
|
|||
Hi George
okay, select E5:E44 and then ensure that row 5 is the first row that you can see at the top of your worksheet .. then choose format / conditional formatting choose formula is type =AND($E5=TODAY()-60,$E5<=TODAY()) click Format .. .set your formatting OK twice does this work better? if not ... zip up the workbook and email it to me at julied_ng at hcts dot net dot au; -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "George" wrote in message ... Julie - I tried the formula and I can't get it to work. When I type in your steps the entire column the background changes to Red ( Which I selected ) regardless of the date.My column is E5:E44 Thanks - George "JulieD" wrote: let us know how you go "George" wrote in message ... Thanks - will give it a try "JulieD" wrote: Hi George to make it change colours you can use Conditional Formatting - select your dates - ensure that the first date is at the top (and visible) of your worksheet choose format /conditional formatting choose formula is type =AND($A2=TODAY()-60,$A2<=TODAY()) where A2 is the first date in your range click on Format choose a formatting - background fill colours are under "pattern" click OK twice -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "George" wrote in message ... On my spread sheet I have a cell to indicate testing due dates - Is there a way to make that cell flash or change colors 60 days prior to the current date ? |
#7
|
|||
|
|||
Julie - I got it to work but the background changes color only for the
current month. What I am trying to do is turn the background red 60 days prior to the current date and include the current month.. Should this formula make that happen ? Thanks "JulieD" wrote: Hi George okay, select E5:E44 and then ensure that row 5 is the first row that you can see at the top of your worksheet .. then choose format / conditional formatting choose formula is type =AND($E5=TODAY()-60,$E5<=TODAY()) click Format .. .set your formatting OK twice does this work better? if not ... zip up the workbook and email it to me at julied_ng at hcts dot net dot au; -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "George" wrote in message ... Julie - I tried the formula and I can't get it to work. When I type in your steps the entire column the background changes to Red ( Which I selected ) regardless of the date.My column is E5:E44 Thanks - George "JulieD" wrote: let us know how you go "George" wrote in message ... Thanks - will give it a try "JulieD" wrote: Hi George to make it change colours you can use Conditional Formatting - select your dates - ensure that the first date is at the top (and visible) of your worksheet choose format /conditional formatting choose formula is type =AND($A2=TODAY()-60,$A2<=TODAY()) where A2 is the first date in your range click on Format choose a formatting - background fill colours are under "pattern" click OK twice -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "George" wrote in message ... On my spread sheet I have a cell to indicate testing due dates - Is there a way to make that cell flash or change colors 60 days prior to the current date ? |
#8
|
|||
|
|||
Hi George
sorry i didn't notice the bit about to the end of the current month ... this, as far as i know is a bit more difficult (-60 days from to today to plus 15 is easy), but to get the last day of the month the only way i know how to do it is to use the EOMONTH function, which is an analysis toolpak function (tools / addins / analysis toolpak) and these can't be used in conditional formatting formulas ... so if you really want -60 from today and to the end of the month, you will need to put the EOMONTH function in a cell somewhere and reference that in your formula: in say cell D6 =EOMONTH(NOW(),1-1) conditional formatting formula now =AND($E5=TODAY()-60,$E5<=$D$6) if however, you do want -60 days to plus 15 days use the conditional formatting formula of =AND($E5=TODAY()-60,$E5<=TODAY()+15) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "George" wrote in message ... Julie - I got it to work but the background changes color only for the current month. What I am trying to do is turn the background red 60 days prior to the current date and include the current month.. Should this formula make that happen ? Thanks "JulieD" wrote: Hi George okay, select E5:E44 and then ensure that row 5 is the first row that you can see at the top of your worksheet .. then choose format / conditional formatting choose formula is type =AND($E5=TODAY()-60,$E5<=TODAY()) click Format .. .set your formatting OK twice does this work better? if not ... zip up the workbook and email it to me at julied_ng at hcts dot net dot au; -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "George" wrote in message ... Julie - I tried the formula and I can't get it to work. When I type in your steps the entire column the background changes to Red ( Which I selected ) regardless of the date.My column is E5:E44 Thanks - George "JulieD" wrote: let us know how you go "George" wrote in message ... Thanks - will give it a try "JulieD" wrote: Hi George to make it change colours you can use Conditional Formatting - select your dates - ensure that the first date is at the top (and visible) of your worksheet choose format /conditional formatting choose formula is type =AND($A2=TODAY()-60,$A2<=TODAY()) where A2 is the first date in your range click on Format choose a formatting - background fill colours are under "pattern" click OK twice -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "George" wrote in message ... On my spread sheet I have a cell to indicate testing due dates - Is there a way to make that cell flash or change colors 60 days prior to the current date ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Math Problem | Excel Worksheet Functions | |||
date and time | New Users to Excel | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |