Create custom calendar

[girapas]

I need to get in Excel 2003 the following:
a) Automatically create a calendar table for each year, as shown below in my example for 2012.
b) Conditional formatting (coloring) Saturdays, Sundays and public holidays.
c) Calculate working (business) days in B40.

I've created separate sheets (one for each year 2012, 2013 etc.) with public holidays. In that sheets there's the date (format dddd,d-mmm-yy), in column A, and the name of the public holiday, in column B.
The date range (column A) is Named as: publ_hol2012, publ_hol2013 and so on.

Thanks in advance

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	2012	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
2	SUN	1			1			1
3	MON	2			2			2			1
4	TUE	3			3	1		3			2
5	WED	4	1		4	2		4	1		3
6	THU	5	2	1	5	3		5	2		4	1
7	FRI	6	3	2	6	4	1	6	3		5	2
8	SAT	7	4	3	7	5	2	7	4	1	6	3	1
9	SUN	8	5	4	8	6	3	8	5	2	7	4	2
10	MON	9	6	5	9	7	4	9	6	3	8	5	3
11	TUE	10	7	6	10	8	5	10	7	4	9	6	4
12	WED	11	8	7	11	9	6	11	8	5	10	7	5
13	THU	12	9	8	12	10	7	12	9	6	11	8	6
14	FRI	13	10	9	13	11	8	13	10	7	12	9	7
15	SAT	14	11	10	14	12	9	14	11	8	13	10	8
16	SUN	15	12	11	15	13	10	15	12	9	14	11	9
17	MON	16	13	12	16	14	11	16	13	10	15	12	10
18	TUE	17	14	13	17	15	12	17	14	11	16	13	11
19	WED	18	15	14	18	16	13	18	15	12	17	14	12
20	THU	19	16	15	19	17	14	19	16	13	18	15	13
21	FRI	20	17	16	20	18	15	20	17	14	19	16	14
22	SAT	21	18	17	21	19	16	21	18	15	20	17	15
23	SUN	22	19	18	22	20	17	22	19	16	21	18	16
24	MON	23	20	19	23	21	18	23	20	17	22	19	17
25	TUE	24	21	20	24	22	19	24	21	18	23	20	18
26	WED	25	22	21	25	23	20	25	22	19	24	21	19
27	THU	26	23	22	26	24	21	26	23	20	25	22	20
28	FRI	27	24	23	27	25	22	27	24	21	26	23	21
29	SAT	28	25	24	28	26	23	28	25	22	27	24	22
30	SUN	29	26	25	29	27	24	29	26	23	28	25	23
31	MON	30	27	26	30	28	25	30	27	24	29	26	24
32	TUE	31	28	27		29	26	31	28	25	30	27	25
33	WED		29	28		30	27		29	26	31	28	26
34	THU			29		31	28		30	27		29	27
35	FRI			30			29		31	28		30	28
36	SAT			31			30			29			29
37	SUN									30			30
38	MON												31
39
40	WORKING DAYS
41
Sheet7

 

[Ron Coderre]

OK...Here you go:
Using your posted scenario
• Format B1:M38 with a custom format that only displays the day number
...CTRL+1...Number_tab...Category: Custom...Type: D...Press: Enter
• B2: =IF(ISNUMBER(B1),B1+1,IF(TEXT(WEEKDAY("1-"&B$1&"-"&$A$1),"DDD")=$A2,--("1-"&B$1&"-"&$A$1),""))
• B9: B8+1
• Copy B9 down through B28
• B29: =IF(OR(MONTH(B28+1)<>MONTH(B28),B28=""),"",B28+1)
• Copy B29 down through B38
• Copy B2:B38...Select B2:M38...Press: ENTER

For the holidays:
On a tab named HolidayTab
• List all holidays for all years beginning in cell A1.
• Name the Col_A date list: rngHolidays

Example for 2012...cells A1:B13
Monday, January 02, 2012             New Year's Holiday 2012
Monday, January 16, 2012             MLK Birthday
Monday, February 20, 2012            Presidents Day
Monday, May 28, 2012                 Memorial Day
Wednesday, July 04, 2012             Independence Day
Monday, September 03, 2012           Labor Day
Monday, October 15, 2012             Columbus Day
Thursday, November 22, 2012          Thanksgiving
Friday, November 23, 2012            Day After Thanksgiving
Monday, December 24, 2012            Day before Xmas
Tuesday, December 25, 2012           XMas Day
Monday, December 31, 2012            Day before New Year
Tuesday, January 01, 2013            New Year's day 2013

Back to the calendar sheet
• Select B2:M38, with B2 as the active cell
• Format.Conditional_formatting
...Formula is: =ISNUMBER(MATCH(B2,rngHolidays,0))
...Click: Format...set the fill color for holiday cells
...Click: OK

That's it.