Microsoft Excel Array Formula with dates

Question

I need the following array for a Date type:

=IF(OR(A2:C2="Mar 12"),1,0)

Sample Data https://docs.google.com/spreadsheet/ccc?key=0AhoDU0OTM87sdHR3RGw3NTJacEV1OEt5OWZTWTBYUFE&usp=sharing

Looking at the sample data, in Column E I want an array formula that would look into columns B to D and search for results that contain 'May 12' and display true or false (1 or 0) in column E.

Peter L. · Accepted Answer · 2013-02-06 16:26:20Z

3

I think this will meet your needs - type in D2:

=IF(SUM((MONTH(A2:C2)=5)*(YEAR(A2:C2)=2013))>0,1,0)

but press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in {} brackets around it (but do NOT type them manually!).

Specify month and year as you wish)

answered Feb 6, 2013 at 16:26

Peter L.

7,3545 gold badges37 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter L. Over a year ago

Glad to help buddy) Hope you learned the lesson of proper task formulation)

Joseph · Accepted Answer · 2013-02-06 16:32:31Z

0

I think this is what you're looking for (noticed the date format was dd/mm/yyyy so I adjusted):

=IF(COUNTIF(A1:C1,"12/5/2013")>0,TRUE,FALSE)

Edit:

For just month and day:

=IF(SUMPRODUCT((MONTH(A1:C1)=5)*(DAY(A1:C1)=12))>0,TRUE,FALSE)

Month and year:

=IF(SUMPRODUCT((MONTH(A1:C1)=5)*(YEAR(A1:C1)=2012))>0,TRUE,FALSE)

edited Feb 6, 2013 at 16:32

answered Feb 6, 2013 at 16:18

Joseph

5,2001 gold badge27 silver badges26 bronze badges

1 Comment

amateur Over a year ago

How do I do it without specifying a 'Day' though? Just month and year?

Collectives™ on Stack Overflow

Microsoft Excel Array Formula with dates

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related