Using Year part of date with conditional formatting

[Alain]

I am creating an attendance tracking tool based on a template I downloaded from the Microsoft Office Downloads site. The statement I am using as the conditional formatting formula is:

=(MONTH(H9)=MONTH($C9))*(COUNTIFS(lstDataName,valSelectName,lstDataYear,valSelectYear,lstDataDate,"<="&H9,lstDataDate,">="&H9,lstDataReason,"Vacation")>0)

I have a date column in my data sheet (YYYY-MM-DD), and I tried everything I can think of to extract the year from the date for the above formula and nothing works. The statement works exactly as it should with the helper column (lstDataYear) I created, but would prefer to do it in the formula.

I am also having a conditional formatting issue, that I will post a second question for.

Any thoughts? (workbook attached)

 

[Luke M]

I think you can change the COUNTIFS to a SUMPRODUCT, and get the flexibility you need
=(MONTH(H9)=MONTH($C9))*(SUMPRODUCT((lstDataName=valSelectName)*(YEAR(lstDataDate)=valSelectYear)*(lstDataDate<=H9)*(lstDataDate>=H9)*(lstDataReason="Vacation"))>0)

 

[Alain]

That solved the problem. Does this functionality not work with countifs?
Thanks Luke.

 

[Luke M]

Correct, you can't manipulate the data being fed into COUNTIFS because it's looking for a range, not an array. Not sure why MS wrote it that way, but's it's what we've got.

PS. The other nice trick with SUMPRODUCT is when you also use TEXT function. Let's you specificy month and year, by doing something like:
TEXT(DataRange,"yyyymm")="201401"