Excel 2016 Formula Help

Highlighted
Occasional Visitor

I am trying to create a conditional formatting formula that will automatically return a value of "M" when two conditions are met without using a MACRO, but I just can't figure it out on my own. Any assistance would be greatly appreciated.

 

On Sheet1 H4 I want to return a value of "M" if true and "" if false using data from Sheet2

I3:NJ3 is a range of dates for the entire year

I5:NJ5 is the range that I input codes 

for example:

 

IF Range I3:NJ3 has a date that =(TODAY) and if Range I5:NJ5 has cell value "M" in the same column that equals (TODAY) if true then Sheet1 cell H4 Equals "M" if false then ""

 

I've tried the following formula. directly in SHEET1 H4 but it keeps telling my I've entered too few arguments for this function. 

 

=IF(AND('SHEET2'!I5:NJ5="M",DATE('SHEET2'!I3:NJ3)=DATE(TODAY()),"M","")

 

What am I doing wrong and how can I make it a conditional rule?

1 Reply

@BigDave_WA 

You are missing arguments for the DATE() function:

The DATE function returns the sequential serial number that represents a particular date.

Syntax: DATE(year,month,day)

On the other hand you might mistake the DATE() function as your values in row 3 are already dates.

So your formula should read as follows:

=IF(AND('SHEET2'!I5:NJ5="M",'SHEET2'!I3:NJ3=TODAY()),"M","")