Google Sheets Conditional formatting based on multiple conditions

Question

I had a spreadsheet in google sheets in my last company which highlighted all my rows based on my qualifying conditions. I can't remember what conditional formatting I used.

I have tried the =IFAND and =AND functions along with others.

This is what I am trying to do:

If column B says DTC and the dates are between two ranges I want it highlighted. Then I will have multiple rules that vary in dates and words.

This is the last formula I tried.

=AND($B2="DTC",$E2>="10/1/2017",$E2<="10/6/2018")

I put google sheets but it said error and recommended excel so I changed it. — Tiffany Poche
– Tiffany Poche, Commented Jan 4, 2018 at 16:33
no, sorry, my expertise is in Excel and that formula should work in Excel. The only suggestion I would make is to make sure the dates in Column E are actual dates and not text that looks like dates. — Scott Craner
– Scott Craner, Commented Jan 4, 2018 at 18:10

Wicket · Accepted Answer · 2018-01-04 21:44:13Z

0

If you enconclose dates between quotations marks they will be treated as strings, not as dates. Used DATE(year,month,day) instead.

answered Jan 4, 2018 at 21:44

Wicket

39.6k9 gold badges81 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Tiffany Poche Over a year ago

It looked like it was working until I added the second set of dates (end date). =AND($B2="DTC",E$2>=DATE(2017,10,1),E$2<=DATE(2018,10,1))

Wicket Over a year ago

@TiffanyPoche: Please create and share a demo spreadsheet including sample input and the expected result (a column with the resulting format applied manually)

Tiffany Poche Over a year ago

[link] (docs.google.com/spreadsheets/d/…)

Wicket Over a year ago

@TiffanyPoche : Did you applied the conditional formatting to the demo spreadsheet? I would like to see how do you applied it.

Wicket Over a year ago

@TiffanyPoche Your data start on row 1 but your formula looks on the row below. Replace $B2 by $B1 and so on.

|

Collectives™ on Stack Overflow

Google Sheets Conditional formatting based on multiple conditions

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related