CODEWITHSUNDEEP
excel
Jimmy
Jimmy

Reputation: 12497

Check if month and year appears in a list in excel

I have a source cell which defines a date (01/01/2015) in cell A2. I want to check if any of the dates in the range of cells K2 to K10 are within that range of month (i.e Janurary) and year (2015).

This is the closest I could get myself:

=IF(MONTH(K2:K10)=A2,1,0)

I also tried this:

=IF(MONTH(K2:K10)=MONTH(A2),1,0)

However this gives me the error: #Value!

Can anyone help me figure out what I am doing wrong please?

Upvotes: 0

Views: 957

Answers (3)

Bathsheba
Bathsheba

Reputation: 234785

Your problem is due to the "blank" cells actually being empty strings.

A trick is to use IFERROR:

=SUM(IF(IFERROR(MONTH(K2:K10*1),0)=MONTH(A2),1,0)>0,1,0)

The *1 will fail for a blank string which, in turn, will fail the subsequent comparison.

Note that this is a single-cell array formula. Use Ctrl + Shift + Return to enter it.

Upvotes: 2

PaulG
PaulG

Reputation: 1199

Possibly something like this:

=SUM(IF(MONTH(K2:K10)=MONTH(A2);1;0))

Needs to be an array formula (press ctrl+shift+enter) This will return 1 or 0 depending upon the date you enter in A2.

Upvotes: 1

sarah.ferguson
sarah.ferguson

Reputation: 3257

Office support states:

The MONTH function syntax has the following arguments:

Serial_number   Required. The date of the month you are trying to find.

Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

How are you entering date? if you enter them by hand you need to parse the date with something like this excel formula

Upvotes: 1

Related Questions