Conditional Formatting Issue

[ExcelIdiot5]

I am having alot of trouble with a spreadsheet and conditional formatting.

We have a CALIBRATION DATE PERFORMED column. Its the date a gage gets calibrated and the date its done is entered into the cell.

We want the spreadsheet to automatically view the date its opened and compare to that cell and shade the cell accordingly. The calibration is late if its after 365 days from the last calibration.

If the todays date minus the cell date is less than 305 its green.
If todays date minus the cell date is 305 to 365 days its yellow. ( 2 months or less from a year)
If todays date minus the cell date is over 365 days its red.

I am having trouble with the yellow formatting.
Also having trouble having each cell recognize its own date.


Can anyone help. Im dying here. LOL

 

[Fluff]

Cross posted Help with Conditional Formatting - Due Date Color Scale

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[Fluff]

How about

SAMPLE color scale.xlsx
	A	B	C	D
1	PART #	E.I. LEVEL	PART NAME	FIXTURE RECERT DATE
2	LJ6B-S40404-AA	NB00-E-13980482-000	PNL. L/G O/S	13/07/2019
3	LJ7B-S24704-AC	NB00-E-13929743-000	PNL. RR DR OTR RH	12/07/2019
4	LJ7B-S24705-AC	NB00-E-13929743-000	PNL. RR DR OTR LH	22/08/2019
5	LJ7B-S20204-AD	NB00-E-13916546-000	PNL DR O/S RH	22/10/2019
6	LJ7B-S20205-AD	NB00-E-13916546-000	PNL DR O/S LH	23/10/2019
7	LJ6B-S20204-AC	NB00-E-13929738-000	FRT. DR. OTR RH	14/09/2020
8	LJ6B-S20205-AC	NB00-E-13929738-000	FRT. DR. OTR LH	14/09/2020
9	LJ6B-S24705-AC	NB00-E-13929741-000	RR DR OTR LH	10/09/2020
10	LJ6B-S24704-AC	NB00-E-13929741-000	RR DR OTR RH	10/09/2020
11	LJ6B-S40405-AE	NB00-E-14032581-001	LIFTGATE OTR UPR	02/09/2020
12
13
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D14	Expression	=AND(D2<>"",D2<TODAY()-365)	text	NO
D2:D14	Expression	=AND(D2<>"",D2<TODAY()-30)	text	NO
D2:D14	Expression	=AND(D2<>"",D2<TODAY()+305)	text	NO

 

[ExcelIdiot5]

I will give this a try.

How can I paste the formula into the other cells so that D3 is referencing D3 date instead of D2.

 

[Fluff]

As long as you select D2 to end of data in col D the formula will do that automatically.
As you can see from my post I selected D2:D14 & the cells got formatted correctly.

 

[ExcelIdiot5]

Amazing. Thank you for this help.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ExcelIdiot5]

Hmm, this is still not working as intended when I copy and paste the formula into my real spreadsheet.
I have it with the rule hierarchy the same as your example and I am getting inaccurate fills.

I have dates listed as 3/20/20 that are yellow. Those should be green. Any ideas?

 

[Fluff]

I think I misunderstood what you wanted, how about
yellow
=AND(D2<>"",D2<TODAY()-305)
green
=AND(D2<>"",D2<TODAY())

 

[ExcelIdiot5]

That seems to have done it.

Thanks again.