If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Question about syntax for conditional formatting
See below:
Can someone explain the following in simple language; =AND($B$12"",$B$12TODAY()+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
|
|||
|
|||
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$12TODAY()+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$12TODAY()+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$12TODAY()+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
|
|||
|
|||
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$12TODAY()+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
|
|||
|
|||
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$12TODAY()+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$12TODAY()+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$12TODAY()+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
|
|||
|
|||
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$12TODAY()+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$12TODAY()+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$12TODAY()+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
|
|||
|
|||
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$12TODAY()+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
|
|||
|
|||
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$12TODAY()+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$12TODAY()+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$12TODAY()+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. |
Thread Tools | |
Display Modes | |
|
|