#ExcelPedroWave

Now I propose a Biannual Mini Calendar with automatic Holidays in Excel I've posted on AyudaExcel.com - CALENDAR (From 1900 to 9999) in two versions:

• MiniCalendarioFestivosPW1.xls in Excel 2003 with 22 conditional formatting rules.
• MiniCalendarioFestivosPW1.xlsx in Excel 2007 and 2010 with 6 conditional formatting rules.

If you have installed Excel 2003 you can improve that version because, with its limited conditional formatting, is more difficult to get mark on Saturday and today and I have not tried hard enough, which is very easy in later versions of Excel.

Features of this mini-calendar:

• Is biannual from 1900 to 9999 as indicated by the subject.
• Is bilingual in English and Spanish.
• In Spanish the first letter of the months in uppercase.
• The names of the months and days in multiple languages by changing the Regional Settings of Windows.
• First week day on Monday (Spanish) or Sunday (English).
• List of programmable or automatic holidays for two years.
• Automatic calculation of Easter Sunday until the year 2202.

Cell A1 checks if the decimal sign is a comma (,) or period (.):

=LEFT(RIGHT((B2+IF(ISEVEN(B2),1,0))/2,2),1)

A2 is equals to A1 and if we write:
- a coma, weeks start on Monday.
- a point, weeks start on Sunday.

In B1 the year is generated randomly at the beginning:

=RANDBETWEEN(1900,2200)

Although you can change it for the current year:

=YEAR(TODAY())

B2 is equal to B1 and is where you type the year.

In B18 the next year is generated or 1900 if 9999:

=IF(B2 < 9999,B2+1,1900)

B19 is equal to B18 and also can be written for another year either.

In AP40 is calculated the Easter Sunday for years less than 2203 with:

=IF($B$2>2203,"",TRUNC(DATE($B$2,7,-CODE(MID("NYdQ\JT_LWbOZeR]KU`",MOD($B$2,19)+1,1)))/7)*7+8)

Same result is generated in AP58 with this award-winning short formula:

=IF($B$19>2203,"",DOLLAR(("4/"&$B$19)/7+MOD(19*MOD($B$19,19)-7,30)*14%,)*7-6)

All cells in the range of days C3:AM14 with custom cell format d;; to hide the days below to 01/01/1900, except the last week of December in the range AE14:AM14 with format [<2958466] d;; to hide above on 31-12-9999.

The most interesting is that this mini-calendar is based on a single formula in C3 to know what the first day of the month is Monday (Spanish) or Sunday (English):

=A3+1-WEEKDAY(A3,IF($A$2=".",1,2))

D3=A3+1 and drag to the right to AM3.
And just drag down the range C3:AM3 to build the minicalendar of a year.

To see those who have not installed Excel 2007 or 2010 I have posted on my ExcelWebApp virtual disk that does not allow validation of data and vertical alignment of text in the cells but you can change the calendar year in cell B2 and enter holidays in column AP:



Download Excel 2007-2010 version here:

Mini Calendar Holidays PW1.xlsx

Download Excel 2003 version here:

MiniCalendarioFestivosPW1.xls

I thank you in advance for sharing your comments about these calendars with me.