0

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
0

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

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

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.