Applying three datedif formulas to one cell

Hello,

I'm having a difficult time getting straight forward guidance on a specific set of conditional formulas.

User Story: I am a training manager needing to color code cells in the Training Date (I) column to reflect the following conditions:

1. 1 year or more, past date of last training = highlight red

2. Greater than or equal to 9 months past date of last training but less than 1 year = highlight yellow

3. Less than 9 months since date of last training = highlight in green

I've tried what's below. I think it's getting close but could really use some help.

=DATEDIF($I1,TODAY(),"y")>1 = RED

=DATEDIF($I5,TODAY(),"m")>=9 = YELLOW

=DATEDIF($I5,TODAY(),"m")<=9 = GREEN

BERT column I.png

Thanks Quang,

I've included a screenshot of the column. After making your suggested change the red condition appears to be working but there are conflicts with the yellow and green.

Any ideas?

=MATCH($I5,EDATE(TODAY(),{0;-12;-9})*{0;1;1})

would be 1 for I5 more than a year before TODAY(), 2 for I5 between 12 and 9 months before TODAY(), and 3 for less than 9 months before TODAY().

Hi Quang,

No luck. I have attached a sample. Thank you sir.

-Sean

Sorry. I didn't test, and I forgot conditional formatting formulas can't use array constants.

If you define the names __cf_array_1 referring to ={0;-12;-9}, and __cf_array_2 referring to ={0;1;1}, then with all cells selected and H5 the active cell,

more than a year old:

Formula:

Please Login or Register  to view this content.

between 9 and 12 months old:

Formula:

Please Login or Register  to view this content.

less than 9 months old:

Formula:

Please Login or Register  to view this content.

Thank you hrlngrv,

I see the formulas worked in the sample spreadsheet. When I attempt to input them into my original, only cells with no value show up as yellow. The rest have no highlighting. I'm thinking something might be wrong with the formatting in the original spreadsheet. I'm curious why the new formulas reference column H?

Thank you,

Sean

Originally Posted by fish1775

. . . I'm curious why the new formulas reference column H? . . .

The sample workbook you provided had a formatted range spanning columns H to J with the topmost cell in row 5. I may have misinterpreted the sample workbook. I treated each column as containing a date which should be evaluated. If only column I should be, and its result applied to columns H to J, then use $I instead of H.