Biannual Mini Calendar with Holidays

Posted on martes, febrero 08, 2011 by Pedro Wave

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 (.):

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:

Although you can change it for the current year:

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

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

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:

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

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):

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.

Traducción al español aquí.

3 Response to "Biannual Mini Calendar with Holidays"

.
gravatar
Eijaz Sheikh Says....

Hi,

you have not posted the excel 2003 version. most of the other formats e.g. for saturday, sunday donot show up. if i select a cell and then check its conditional formatting the conditional formatting formula appear different each time and also the color for the cell toggles between green, pink, red.

.
gravatar
Pedro Wave Says....

I just uploaded the excel 2003 version in the post but I repeat what was said above, if you have 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.

.
gravatar
Anónimo Says....

Si quiero hacer unas asistencias para estudiantes por parciales, por ejemplo empieza el parcial el 24/04/2017 y termina el 10/06/2017 como hago para poner las fechas de inicio y fin del parcial y solito se pongan las fechas para tomar las asistencias, de ese parcial y el próximo año solo cambie las fechas y se ponga automáticamente y no tener que ver el calendario y hacerlo manualmente

Leave A Reply

Dime si te gusta lo que lees y, si no te gusta, dime por qué. Tengo habilitada la moderación de comentarios. Tu comentario se publicará pronto.

Tell me if you like what you read here and if you don't like, tell me why. I've enabled comment moderation. Your comment will be published ASAP.

Mi Lista de Blogs- My Blog List