SOLVED

Conditional formatting of a data point in a dated row based on date range and greater than a value

Copper Contributor

Hello! Does anyone know if there is a way to conditionally format data points in excel to automatically change color based on 1) a date listed on that row, and 2) that is also greater than a specific value? To be more specific, I work in a lab, and test results for multiple tests taken on the same day are listed across a row.  The date is listed in column 1. I need the color change to happen when the results are greater than or equal to a specific number, but that specific number is different in the winter months than in the summer months. So I would need to be able to specify that if the date in column 1 is between the months of May through October, then the color change needs to happen greater than or equal to X, but in the months of November through April, the color change needs to happen greater than or equal to Y. This is maybe confusing sounding, but I'm hoping someone can help!

5 Replies

@cavallda 

Maybe as shown in the attached file. You can dynamically enter X and Y in cells K2 and L2.

@OliverScheurich
Thanks for your response! So it looks maybe like that’s what I need, but I will admit, I am not very savvy when it comes to tech stuff like this. Any idea how I might actually type that into the conditional formatting rule?

@OliverScheurich 

Capture.JPG

 Okay so here is what I'm talking about. So under the column entitled NH3-N, there are the results from specific dates (all samples taken on the same date are in a row). The allow limits are listed above, so in the winter, I need the color change at greater than or equal to 1.4, but in the summer I need the color change at greater than or equal to 1.3. But ONLY for the results listed in that one column. I just don't know how I would actually write that rule.... (I absolutely appreciate the help, by the way!)

best response confirmed by cavallda (Copper Contributor)
Solution

@cavallda 

Maybe as shown in the attached file.

This is it!!! I cannot thank you enough! Your help is MUCH appreciated!
1 best response

Accepted Solutions
best response confirmed by cavallda (Copper Contributor)