Conditional Formatting - based on two dates being >=30 days from today

[Alice1]

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

 

[arthurbr]

=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.

Strange, this looks like a circular reference? Where do you enter the formula, not in A1 I presume
When you say " does not seem to work", what do you mean?

 

[Alice1]

Sorry yes, should read :

=IF(C1="","",IF(TODAY()-30>C1,TODAY()-C1,""))

and by 'does not seem to work' I mean Conditional Formatting is not actioned (ie the Row would turn Pink) if cells A1 and B1 are >=30

 

[arthurbr]

I could be wrong but not knowing what C1 contains I was wondering :
TODAY-30>C1 is the same as TODAY-C1>30

so your formula gives if (TODAY-C1>30,TODAY-C1... always returns a number larger than 30. So what is the CF for?

Shouldn't it be IF(C1-TODAY()>30... ? But even so, the result will always be larger than 30?

 

[Alice1]

Firstly, thanks for your responses and observations.

I've uploaded a simplified version of the file :

https://app.box.com/s/3syjezptl0jkv83itvsd6c6be7yyqw0w

basically it's lots of staff Login dates and last password changes for our network. Where a member of staff has not logged in for >=30 days AND not changed their password in the last 30 days too I'd like to highlight their Row.

 

[Alice1]

Anybody able to help me please ?

 

[Fluff]

Is this what you want for the CF formula
=AND($C3<>"",$C3<today()-30,$e3<>"",$E3<today()-30)< html=""></today()-30)<></today()-30,$e3<>< today()-30)

 

[Alice1]

Conditional Formatting formula :

=AND($B3<>"",$D3<>"")

worked perfectly, thank you very much.