Cell value change with Month

debugcn Published at Dev

PanchoVilla

I'm trying to have certain dates out of the week from day 1 to the last day of the month when I type a month in a cell for example:

If cell "I6" Contains "March" or whatever month:

                      " MARCH "

then:

cell "F10" = 1-Mar for- Sun (first Sunday of the month)
Cell "F11" = 5-Mar for -Thur (First Thursday of the month)
Cell "F12" = 8-Mar for -Sun (second Sunday of the the month and so on)
Cell "F13" =12-Mar for -Thur

Is this possible? I have look in different sites but no luck many thanks.

Rajesh S

You may use this also:

How it works:

In Cell K23, enter 1st Date of the Month (like I've used 02/01/2020, format is, MM/DD/YYYY ).
Apply MMM YY to the Cell.
Formula in cell K25:

=K$23+MOD(7-WEEKDAY(K$23,2),7)

Next, formula in Cell K26:

=K$23+MOD(4-WEEKDAY(K$23,2),7)

N.B.: In above formula 7 & 4 represents Sunday and Thursday, and you may edit then as your need.

Final formula in Cell K27 & fill it down:

=IFERROR(IF(AND(MONTH(K26)=MONTH(K$23),COUNT(J$25:J25)=7),"",K25+7),"")

Now enter this formula in Cell J25 & fill down.

=IF(ISBLANK(K25),"",K25)

Edited:

OP has raised a good point that how to manage dates if and when Sunday & Thursday are more than 8 (like in May, August Sundays are 5 and Thursdays are 4).

Enter this Formula in Cell L25:

=SUMPRODUCT(N(TEXT(ROW(INDIRECT(K25&":"&EOMONTH(K25,0))),"ddd")="Sun"))+SUMPRODUCT(N(TEXT(ROW(INDIRECT(K25&":"&EOMONTH(K25,0))),"ddd")="Thu"))

N.B. This Counts Sundays & Thursdays, where Sun and Thu are editable.

Formula in K27:

=K$25+MOD(7-WEEKDAY(K$25,2),7)

Formula in Cell k28:

=K$25+MOD(4-WEEKDAY(K$25,2),7)

Enter formula in Cell K29:

  =IFERROR(IF(AND(MONTH(K28)=MONTH(K$25),COUNT(J$27:J27)>=(L$25-1)),"",K27+7),"")

N.B.

Use of MONTH check & ROW COUNT with formula in Cell K27 & K29, restricts Excel to produce only DATEs for all Sundays & Thursdays.
Now keep changing Date & Year in cell K23 or in K25 as needed.
Adjust cell references in the formula as needed.

Collected from the Internet

Please contact [email protected] to delete if infringement.