Hi
I assume you use US weekday counting, i.e. Sunday is the 1st day of the week?
When this is the case, then:
1. Add an additional row to your calendary so you don't lose some month's endings;
2. Extend your months list in column O (and fill all gaps in it, so you have all months from January to December listed (Range O2:O13);
3. Copy this formula into leftmost upper cell of your calendary (D9):
=IF(MONTH(DATE($L$12,MATCH($L$9,$O$2:$O$13,0),1)-(WEEKDAY(DATE($L$12,MATCH($L$9,$O$2:$O$13,0),1))+0 ) + (COLUMN()-COLUMN($C:$C))+7*(ROW()-ROW($9:$9)))=MATCH($L$9,$O$2:$O$13,0),DATE($L$12,M ATCH($L$9,$O$2:$O$13,0),1)-(WEEKDAY(DATE($L$12,MATCH($L$9,$O$2:$O$13,0),1))+0 ) + (COLUMN()-COLUMN($C:$C))+7*(ROW()-ROW($9:$9)),"")
(Check the formula for semicolons instaed of comma for case I missed some replacements there!)
4. Copy the formula in D9 into entire calendary table (D9:J14). Format calendary cells as custom "d".
|