Value & Date Validation

[Equin0x]

Hello

I'm trying to work out a formula that will do the following:

If E8 = "Yes" and F8 is greater than 14 days old from todays date, write "Older than 14 days" in J8.

I can then copy this formula down through E8:F1000, as there will be upto 1000 rows to perform the validation on.

=IF((TODAY()-$F13)>14,"Over 14 Days","")

This works for checking just the date, but doesn't include the Yes check as well. I've tried

=IF(E13="Yes"&(F13>=TODAY()-14),"T","F")

and various other ways, but I can't seem to make this work.

Any help greatly appreciated, thanks.

 

[moonfish]

=IF(AND((TODAY()-$F9)>14,E13="Yes"),"Over 14 Days","")

The & is used to concatenate two strings. You needed the AND() operator.

 

[marka87uk]

Use AND()

=IF(AND(E13="Yes",F13>=TODAY()-14),"T","F")

 

[VoG]

Try

=IF(AND(E8="Yes",TODAY()-$F8>14),"Over 14 Days","")

 

[Equin0x]

Amazing, thanks guys, works great. I was forgetting all about AND. Schoolboy error

 

[Equin0x]

Hi

Would it be possible to slightly extend the functionality of this, possibly requiring turning it into VBA, so that it covers 3 validation rules.

=IF(AND($B2="Accepted",TODAY()-$C2>21),"Over 3 weeks","")
=IF(AND($B2="Requested",TODAY()-$C2>42),"Over 6 weeks","")
=IF(AND($B2="Pending",TODAY()-$C2>2),"Over 2 days","")

Thanks.

 

[VoG]

Try

=IF(AND($B2="Accepted",TODAY()-$C2>42),"Over 3 weeks",IF(AND($B2="Requested",TODAY()-$C2>42),"Over 6 weeks",IF(AND($B2="Pending",TODAY()-$C2>2),"Over 2 days","")))