1

I have a date field, where I would like to do some conditional formatting.

So, if the date is more than 2 weeks ago AND the neighboring cells contains the words "Open" or Reminder" I would like the date cell to have a red background.

Unfortunately I can't get any formula to work. Any help would be appreciated.

Improve this question
1
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. You can even upvote the answer. If you don't have enough reputation, your upvote will show up later, once you reach enough reputation points.
    – marikamitsos
    Jul 20, 2022 at 16:59

3 Answers 3

Reset to default
1

Assuming that Open and Reminder may be in some cells of column A and the dates are in column B, this formula applied to the conditional formatting of column B should solve your problem:

=and(or(A2="open",A2="reminder"),today()>=B2+14,B2<>"" )

link to the test file

Improve this answer
0

You do not quite specify the "neighboring" cells.
You also ask: "to adapt the formula, to paste it over the complete row"

So here are 3 options.
You choose the one best suited to your needs.

  1. Apply on just the date column when the column before it contains open or reminder (orange in our example)

=AND($R3<=TODAY()-14,OR($Q3="open",$Q3="reminder"),$R3<>"")

  1. Apply on a whole range when the column before the date column contains open or reminder (red in our example)

=AND($R3<=TODAY()-14,OR($Q3="open",$Q3="reminder"),$R3<>"")

  1. Apply on a whole range when the column before or after the date column contains open or reminder (yellow in our example)

    =OR(AND($R3<=TODAY()-14,OR($Q3="open",$Q3="reminder"),$R3<>""),AND($R3<=TODAY()-14,OR($S3="open",$S3="reminder"),$R3<>""))

enter image description here

(Do adjust the formulae according to your ranges and locale)

Improve this answer
0

It was indeed the semicolon, now the formula is valid.

I've adapted the formula according to my needs now and it works (the row turns pink):

=and( or($F1="Offen";$F1="Erinnert");today()>=$A1+14;$A1<>"" )

Google Sheet

However, I would need a second function with the same parameters, but when the date has been 4 weeks ago it needs to turn a different colour (bright red).

I've adapted the formula accordingly:

=and( or($F1="Offen";$F1="Erinnert");today()>=$A1+28;$A1<>"" )

And added a new rule. So now I have both rules applied: Conditional Format Rules

But only the first formula with the 14 days gets applied. Dates from 4 weeks ago are formatted the same as dates from 2 weeks ago.

So, how can I adapt the formula, so both rules get applied?

Improve this answer
1
  • just move the condition valid after 28 days up to the first position
    – Daniele
    Aug 25, 2022 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.