Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about syntax for conditional formatting
See below:
Can someone explain the following in simple language; =AND($B$12<"",$B$12<TODAY()+120) What does the < after the cell location mean? and why the double quotes ""? I thought the double quotes meant whatever is in the cell is NULL....I have a date in the $B$12 position. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about syntax for conditional formatting
Hi
The formula will evaluate as TRUE or FALSE and is checking 2 conditions which must both be TRUE =AND($B$12<"",$B$12<TODAY()+120) the AND is the bit that ensure both must be true to return TRUE Condition 1 checks that B12 has data in the cell i.e < means 'does not equal' $B$12<"" Condition 2 checks that the date in the cell is less than (<) 120 days in the future $B$12<TODAY()+120 The reason condition 1 is required is that B12 would evaluate as zero if it was empty and cause the formula to evaluate as TRUE -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Cbeckwith" wrote: See below: Can someone explain the following in simple language; =AND($B$12<"",$B$12<TODAY()+120) What does the < after the cell location mean? and why the double quotes ""? I thought the double quotes meant whatever is in the cell is NULL....I have a date in the $B$12 position. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about syntax for conditional formatting
The condition becomes true if cell $B$12 is not blank and the date in it is
earlier than today + 120 days. -- Regards! Stefi €˛Cbeckwith€¯ ezt Ć*rta: See below: Can someone explain the following in simple language; =AND($B$12<"",$B$12<TODAY()+120) What does the < after the cell location mean? and why the double quotes ""? I thought the double quotes meant whatever is in the cell is NULL....I have a date in the $B$12 position. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about syntax for conditional formatting
Mike, Thanks for this information, very helpful. Now my question becomes if
the B12 date is a future date, not a past date, how can I write the formula to look at the date (B12) in comparision to Today's actual date + 120 days into the future? Do I just change the < to a today()+120 ? "Mike H" wrote: Hi The formula will evaluate as TRUE or FALSE and is checking 2 conditions which must both be TRUE =AND($B$12<"",$B$12<TODAY()+120) the AND is the bit that ensure both must be true to return TRUE Condition 1 checks that B12 has data in the cell i.e < means 'does not equal' $B$12<"" Condition 2 checks that the date in the cell is less than (<) 120 days in the future $B$12<TODAY()+120 The reason condition 1 is required is that B12 would evaluate as zero if it was empty and cause the formula to evaluate as TRUE -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Cbeckwith" wrote: See below: Can someone explain the following in simple language; =AND($B$12<"",$B$12<TODAY()+120) What does the < after the cell location mean? and why the double quotes ""? I thought the double quotes meant whatever is in the cell is NULL....I have a date in the $B$12 position. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about syntax for conditional formatting
This formula is just giving me "Pink" color on background no matter what date
I give it in the b12 =AND($B$12<"",$B$12TODAY()+60) Formats to pink background =AND($B$12<"",$B$12TODAY()+120) Formats to a yellow background =AND($B$12<"",$B$12TODAY()+180) Formats to a green background According to what I see, it should give me the different colors depending. "Mike H" wrote: Hi The formula will evaluate as TRUE or FALSE and is checking 2 conditions which must both be TRUE =AND($B$12<"",$B$12<TODAY()+120) the AND is the bit that ensure both must be true to return TRUE Condition 1 checks that B12 has data in the cell i.e < means 'does not equal' $B$12<"" Condition 2 checks that the date in the cell is less than (<) 120 days in the future $B$12<TODAY()+120 The reason condition 1 is required is that B12 would evaluate as zero if it was empty and cause the formula to evaluate as TRUE -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Cbeckwith" wrote: See below: Can someone explain the following in simple language; =AND($B$12<"",$B$12<TODAY()+120) What does the < after the cell location mean? and why the double quotes ""? I thought the double quotes meant whatever is in the cell is NULL....I have a date in the $B$12 position. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about syntax for conditional formatting (RESOLVED)
Thank You for all your comments, Very useful.
"Cbeckwith" wrote: See below: Can someone explain the following in simple language; =AND($B$12<"",$B$12<TODAY()+120) What does the < after the cell location mean? and why the double quotes ""? I thought the double quotes meant whatever is in the cell is NULL....I have a date in the $B$12 position. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about syntax for conditional formatting
Change the order of the three conditions like this:
=AND($B$12<"",$B$12TODAY()+180) Formats to a green background =AND($B$12<"",$B$12TODAY()+120) Formats to a yellow background =AND($B$12<"",$B$12TODAY()+60) Formats to pink background If the first condition evaluates to True, the other two will NOT be evaluated! -- Regards! Stefi €˛Cbeckwith€¯ ezt Ć*rta: This formula is just giving me "Pink" color on background no matter what date I give it in the b12 =AND($B$12<"",$B$12TODAY()+60) Formats to pink background =AND($B$12<"",$B$12TODAY()+120) Formats to a yellow background =AND($B$12<"",$B$12TODAY()+180) Formats to a green background According to what I see, it should give me the different colors depending. "Mike H" wrote: Hi The formula will evaluate as TRUE or FALSE and is checking 2 conditions which must both be TRUE =AND($B$12<"",$B$12<TODAY()+120) the AND is the bit that ensure both must be true to return TRUE Condition 1 checks that B12 has data in the cell i.e < means 'does not equal' $B$12<"" Condition 2 checks that the date in the cell is less than (<) 120 days in the future $B$12<TODAY()+120 The reason condition 1 is required is that B12 would evaluate as zero if it was empty and cause the formula to evaluate as TRUE -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Cbeckwith" wrote: See below: Can someone explain the following in simple language; =AND($B$12<"",$B$12<TODAY()+120) What does the < after the cell location mean? and why the double quotes ""? I thought the double quotes meant whatever is in the cell is NULL....I have a date in the $B$12 position. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Question | Excel Discussion (Misc queries) | |||
Conditional Formatting Question | Excel Worksheet Functions | |||
Another conditional formatting question. | Excel Discussion (Misc queries) | |||
Conditional Formatting Question | Excel Worksheet Functions | |||
Conditional Formatting question | Excel Worksheet Functions |