Conditional Formatting

[Jeff Bedel]

If anyone could offer some insight, I'd be very grateful! I have two calendars on the attached spread sheet. I'm trying to make the bottom calendar conditionally formatted to where the "Points" or "red font cell" drop off after 1 year (365 days) from the current day (today()). I've tried a variety of formulas. The current formula for the conditional format is :
=(MONTH(H43)=MONTH($C43))*(COUNTIFS(lstSdates,"<="&TODAY()-365))*(COUNTIFS(lstEmpNames,valSelEmployee2,lstSdates,"<="&H43,lstEDates,">="&H43,lstPoint4,">0")>0)

I've tried inserting lstSdates,"<="&Today()-365 in various places
I've tried inserting lstSdates,"<="&Edate(Today(),-12) in various places
I cannot seem to get it right. Can anyone offer any assistance on this? Pretty please

 

[Chihiro]

Can you clarify a bit?

Do you want to apply CF for Red Font only if date falls within today-365 days and point > 0?

 

[Jeff Bedel]

Yes the red font only.

 

[Chihiro]

CF formula would be.

=(MONTH(H43)=MONTH($C43))*(COUNTIFS(lstEmpNames,valSelEmployee2,lstSdates,"<="&H43,lstEDates,">="&H43,lstPoint4,">0")>0)*(H43>=Today()-365)

 

[Jeff Bedel]

Wow, literally the only thing I didn't try. You are an excel God ! Thank you!!!