Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Upcoming expiry date warnings that turn cell contents red
Hi
I have a spreadsheet with people's certificates (eg. CPR) with the date the cerficiate expires. I want to be able to open the spreadsheet at any time and have anyone whose certificate is one month from expiring, be red (I want the whole cell to turn red.) I've tried conditional formatting but can't seem to get it. Any help would be greately appreciated!! Thanks:) Heather -- Heather |
#2
|
|||
|
|||
Heather
assuming a header row and that the dates are in column A, then something like: Conditional Formatting Formula is: =TODAY()$A2+30 Copied to all the cells you want to turn red Regards Trevor "Heather via OfficeKB.com" <u14448@uwe wrote in message news:552746cbb541d@uwe... Hi I have a spreadsheet with people's certificates (eg. CPR) with the date the cerficiate expires. I want to be able to open the spreadsheet at any time and have anyone whose certificate is one month from expiring, be red (I want the whole cell to turn red.) I've tried conditional formatting but can't seem to get it. Any help would be greately appreciated!! Thanks:) Heather -- Heather |
#3
|
|||
|
|||
Good evening Heather What I think you need to do is set up a cell somewhere with todays date (=NOW() will always update to todays date when you open the spreadsheet). In my example, cell E4 contains todays date, D4 the expiry date. Go to Format Conditional Formatting and select Formula Is and enter this formula : =(E4-D4)<30 and then set up the cell / font colour etc and click OK. HTH DominicB -- dominicb ------------------------------------------------------------------------ dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932 View this thread: http://www.excelforum.com/showthread...hreadid=472221 |
#4
|
|||
|
|||
Upcoming expiry date warnings that turn cell contents red
Thanks for the response.
Seems to be working! Thank you so much!! Now I can stop pulling my hair out: ) Heather dominicb wrote: Good evening Heather What I think you need to do is set up a cell somewhere with todays date (=NOW() will always update to todays date when you open the spreadsheet). In my example, cell E4 contains todays date, D4 the expiry date. Go to Format Conditional Formatting and select Formula Is and enter this formula : =(E4-D4)<30 and then set up the cell / font colour etc and click OK. HTH DominicB -- Heather Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200510/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell color change based on due date | New Users to Excel | |||
Change cell color dependin on date ... | Excel Worksheet Functions | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) |