Home |
Search |
Today's Posts |
#1
|
|||
|
|||
"If" function and Upcoming Dates
Hello, this is my first post and I could really use some help with the problem. I'm setting up a roster for a boy scout troop. And using an upcoming date in relation to the date in the cell. I need to display one of three colors for that cell. Example: If Scout's med form (date displayed) expires in less than 30 days; fill said cell YELLOW, or if it's past due the cell is RED, if more than 30 days away, said cell is GREEN. All in relation to the current date when reading form.
I'm not sure if this is possible but if it is, I figured someone out there would know how to do it. Thank you for taking the time to read this. |
#2
|
|||
|
|||
Quote:
you select the whole column and choose conditional formatting. Enter below formula: =A2<=(today()-30) Then apply and OK. Same for those 60days ,etc. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"If" function and Upcoming Dates
I need to display one
of three colors for that cell. Example: If Scout's med form (date displayed) expires in less than 30 days; fill said cell YELLOW, or if it's past due the cell is RED, if more than 30 days away, said cell is GREEN. All in relation to the current date when reading form. Excel's "Conditional Formatting" (CF) is designed for this. The details of how to set up the conditions vary in different versions of Excel, so check the built-in Help in your version. Here's one way to proceed. Start by selecting the entire column containing the dates. Then select the command to activate CF. In Excel 2010, it's Home Styles Conditional Formatting Manage rules You'll want to have three rules. When given the choice for each rule, "use a formula" to determine which cells to format. For each rule, you enter a formula and you choose the formatting for that rule, such as cell fill color. Leave the whole column selected while setting the rules. The formulas below assume column A holds the dates; if your dates are in a different column, change the formulas accordingly. The rules should end up in this order. Rule 1 is yellow-filled cell format with formula =AND(ISNUMBER(A1),TODAY()<=A1,TODAY()=A1-30) Rule 2 is red-filled cell format with formula =AND(ISNUMBER(A1),TODAY()A1) Rule 3 is green-filled cell format with formula =ISNUMBER(A1) Hope this helps getting started. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
function to return day in the form "Monday", "Tuesday" etc given . | Excel Worksheet Functions | |||
How do I use the "SUMIF" function using dates as a condition | Excel Worksheet Functions | |||
Please add a "sheet" function like "row" and "column" functions | Excel Programming |