# combination of min and if for dates

its my first question here..............

Que.:- I have 100s of dates in column A. I want to record next Sunday on 1st day of any month. in cell B1 I putted a formula `=IF(AND(WEEKDAY(A:A)=1,DAY(A:A)=1),A:A)` then dragged it in B column, then I have got required dates and FALSE values. then in C1 I used a formula `=MIN(B:B)` I get exact required date.

but I need it in a single formula so I applied a formula `=MIN(IF(AND(WEEKDAY(A:A)=1,DAY(A:A)=1),A:A))` but it did not work.

I am wondering if anyone help me get it in a single formula correctly. thanks! regards, HR

• I don't understand what you want when you write "record next Sunday on 1st day of any month". Can you be more specific, and especially can you give examples of input and desired output -- by providing an example of the dates in column A, and where and what you want for output. – Ron Rosenfeld Feb 14 '15 at 15:27
• @RonRosenfeld Hi "I meant to say that I need the date of first Sunday which comes on 1st day of a Month." for ex:1st March,2015 Thanks! – Hemant Rupani Feb 14 '15 at 15:53
• drive.google.com/file/d/0B4yByr16j47mUEJNUF9jOC1ISWc/… its my working, but formula on the cell D1 doesn't work. – Hemant Rupani Feb 14 '15 at 15:54

The reason that your `MIN(IF` function doesn't work is because you can't use `AND` in those type of formulas, because `AND` will return a single result not the required array - you can either use * to simulate `AND` or use nested IFs, e.g. either

`=MIN(IF((WEEKDAY(A:A)=1)*(DAY(A:A)=1),A:A))`

or

`=MIN(IF(WEEKDAY(A:A)=1,IF(DAY(A:A)=1,A:A)))`

.....but if you want to get the next first of month Sunday after any date in A2 you can use this non-array formula:

`=EOMONTH(A2,MATCH(1,WEEKDAY(EOMONTH(A2,{0,1,2,3,4,5,6,7,8,9,10,11,12,13})+1),0)-1)+1`

There are never more than 14 months between instances so you can examine the next 14 first of month dates and take the first one that's a Sunday

• * & IFs give same. BTW EOMONTH's formula is great and simple – Hemant Rupani Feb 16 '15 at 5:18

I think what you want is the first Sunday after today that occurs on the first day of a month. If that is the case, the following array formula will return that date:

``````=INDEX(ROW(INDIRECT(TODAY()&":"&TODAY()+1000)),MATCH(1,(DAY(ROW(INDIRECT(TODAY()&":"&TODAY()+1000)))=1)*(WEEKDAY(ROW(INDIRECT(TODAY()&":"&TODAY()+1000)))=1),0))
``````

To enter an array formula, hold down `ctrl-shift` while hitting `enter`. If you do this correctly, Excel will place braces {...} around the formula which you can see in the formula bar.

This portion of the formula:

``````ROW(INDIRECT(TODAY()&":"&TODAY()+1000))
``````

creates an array of dates starting with TODAY and extending forward for 1000 days. I picked 1000 because I don't know the longest possible interval between first Sundays on the 1st of a month. Empirically, there seem to be at least one per year, if not two, so a smaller number may be equally effective.