Conditional Formatting - based on two dates being >=30 days from today
Hi all
Simple concept and WORKS, two columns A and B with two numbers entered by hand (ie not via a formula), if BOTH A and B are >=30 per Row then turn the cells pink colour for that Row
A B
1 31 31
2 8 34
3 34 33
Conditional formatting formula is : =AND($A1>=30,$B1>=30)
easy, works every time. in the above example Rows 1 and 3 will be coloured pink, because both conditions in A and B cells for that Row are met.
What does not work (for me) is when the contents are generated by a formula, so :
A B
1 31 31
2 8 34
3 34 33
A1 is the result of a day's elapsed compared to today's date formula eg :
=IF(A1="","",IF(TODAY()-30>A1,TODAY()-A1,""))
so A1 shows 31 if the date entered into another cell is '12/09/2018' and the same for B1.
How do I enter Conditional formatting to test these cells if their content is created by a formula, as Excel the Conditional formatting formula :
=AND($A1>=30,$B1>=30)
does not seem to work in the latter example
Hi all
Simple concept and WORKS, two columns A and B with two numbers entered by hand (ie not via a formula), if BOTH A and B are >=30 per Row then turn the cells pink colour for that Row
A B
1 31 31
2 8 34
3 34 33
Conditional formatting formula is : =AND($A1>=30,$B1>=30)
easy, works every time. in the above example Rows 1 and 3 will be coloured pink, because both conditions in A and B cells for that Row are met.
What does not work (for me) is when the contents are generated by a formula, so :
A B
1 31 31
2 8 34
3 34 33
A1 is the result of a day's elapsed compared to today's date formula eg :
=IF(A1="","",IF(TODAY()-30>A1,TODAY()-A1,""))
so A1 shows 31 if the date entered into another cell is '12/09/2018' and the same for B1.
How do I enter Conditional formatting to test these cells if their content is created by a formula, as Excel the Conditional formatting formula :
=AND($A1>=30,$B1>=30)
does not seem to work in the latter example