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"
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.
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.
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
Indícame las erratas que encuentres y qué es lo que te gustaría ver en los próximos artículos.