View Single Post
 
Old 03-25-2013, 07:58 AM
ArviL ArviL is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Feb 2013
Posts: 11
ArviL is on a distinguished road
Default

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".
Reply With Quote