Biannual Mini Calendar with Holidays

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.

Traducción al español aquí.

Mini Calendario Bianual con Festivos

Ahora propongo un Mini Calendario Bianual con días festivos automáticos en Excel que he publicado en AyudaExcel.com - CALENDARIO (De 1900 a 9999) en dos versiones:

• MiniCalendarioFestivosPW1.xls en Excel 2003 con 22 reglas de formato condicional.
• MiniCalendarioFestivosPW1.xlsx en Excel 2007 y 2010 con 6 reglas de formato condicional.

Los que tenéis instalado Excel 2003 podéis mejorar esa versión, ya que con sus limitaciones en el formato condicional es más complicado conseguir marcar los sábados y el día de hoy y no lo he intentado lo suficiente, cosa que es muy fácil en versiones posteriores de Excel.

Características de este minicalendario:

• Es bianual desde 1900 a 9999 como indica el tema.
• Es bilingue en español e inglés.
• En español la primera letra de los meses en mayúsculas.
• Los nombres de los meses y los días en múltiples idiomas cambiando la Configuración Regional de Windows.
• Comienzo de la semana en lunes (español) o en domingo (inglés).
• Listado de festivos programables o festivos automáticos para dos años.
• Cálculo automático de la Semana Santa hasta el año 2202.

En A1 se comprueba si el signo decimal es una coma (,) o un punto (.)

=IZQUIERDA(DERECHA((B2+SI(ES.PAR(B2);1;0))/2;2);1)

A2 es igual a A1 y si se escribe:
- una coma, las semanas empiezan en lunes.
- un punto, las semanas empiezan en domingo.

En A2 se genera un año aleatorio al inicio:

=ALEATORIO.ENTRE(1900;2200)

Aunque se puede cambiar por el año actual:

=AÑO(HOY())

B2 es igual a A2 y es dónde se escribe el año.

En B18 se genera el año siguiente o 1900 si es 9999:

=SI(B2 < 9999;B2+1;1900)

B19 es igual a B18 y se puede escribir otro año cualquiera.

En AP40 se cálcula el Domingo de Pascua de la Semana Santa para años menores que 2203 con:

=SI($B$2>2203;"";TRUNCAR(FECHA($B$2;7;-CODIGO(EXTRAE("NYdQ\JT_LWbOZeR]KU`";RESIDUO($B$2;19)+1;1)))/7)*7+8)

En AP58 lo mismo con esta fórmula más corta y muy premiada:

=SI($B$19>2203;"";MONEDA(("4/"&$B$19)/7+RESIDUO(19*RESIDUO($B$19;19)-7;30)*14%;)*7-6)

NOTA: En Excel 2010 la función EXTRAE se llama MED y la función RESIDUO se llama RESTO.

Todas las celdas en el rango de días C3:AM14 tienen el formato de celdas personalizado d;; para ocultar los días inferiores al 1-01-1900, excepto en la última semana de diciembre en el rango AE14:AM14 con formato [<2958466]d;; para ocultar los días superiores al 31-12-9999.

Lo más interesante es que este minicalendario se basa en una única fórmula en C3 para saber cuál es el primer día del mes que es lunes (español) o domingo (inglés):

=A3+1-DIASEM(A3;SI($A$2=".";1;2))

D3=A3+1 y se arrastra hacia la derecha hasta AM3.
Ya sólo basta arrastrar hacia abajo el rango C3:AM3 para construir el minicalendario de un año.

Para poder verlo los que no tengan instalado Excel 2007 o 2010 lo he publicado en mi disco virtual SkyDrive de ExcelWebApp que no permite validaciones de datos ni alineación vertical de los textos en las celdas pero se puede cambiar el año del calendario en la celda B2 e introducir festivos en la columna AP:



Descarga la versión Excel 2007-2010 aquí:

Mini Calendar Holidays PW1.xlsx


Descarga la versión Excel 2003 aquí:

MiniCalendarioFestivosPW1.xls


Os doy las gracias anticipadas por compartir vuestros comentarios sobre estos calendarios conmigo.

English translation of this post here.

Geocoding the World Clock Map

By showing, in the previous article How to dynamically generate GUI, a world map with time zones illuminated by the sun in real time in which cities are created by geocoding.

This technique is used in Geographic Information Systems - GIS to include cities in a layer within a spatial database in which the points are the geographic coordinates of terrestrial latitude and longitude measurements.

Not treated to compete with the many applications of GIS software on the market, but if you can say that use Excel as a tool for geotagging is cheaper and easy and allows you to add geographic information in the metadata of images that are used for georeferencing.

Contribution of this World Time Zones Clocks Map can be categorized within the Volunteered Geographic Information - VGI movement that disseminate geographic information provided voluntarily by people like this map of clocks that can be seen in this video:

Geolocation of cities

Here's how geolocate cities on the world map.

The first is a list of cities and this is created connecting to this Wordlclock website:
http://www.timeanddate.com/worldclock/full.html?sort=0

Sheet Cities

On the Cities sheet are obtained these columns:
A - The name of the countries, provinces and cities separated by hyphens.
B - The local time in each city.
C - Time Zones.

Using deconcatenate or split techniques, from data in column A are obtained data in columns:
D - Countries.
E - Provinces.
F - The name of the cities.

With the Name Manager, list of cities is defined:
ciudades=Ciudades!$F$3:$F$700

Sheet Coord

This sheet temporarily stores the coordinates of the cities:
Latitude: 38° 30' North
Longitude: 28° 00' West

Next cells contain:
B1 - Latitude in degrees and minutes.
C1 - North or South.
B2 - Longitude in degrees and minutes.
C2 - East or West.

Sheet Clocks

On Clocks sheet two dropdown lists are displayed with data validation into the columns:
R - Select a city.
S - Select a country.

The change event, in any cell in this sheet, executes this routine:
Private Sub Worksheet_Change(ByVal Target As Range)

If a city or country changes in columns R and S, this routine is called:

If ActiveSheet.Name = "Relojes" Then
    If Not Application.Intersect(Target, Range("R:S")) Is Nothing Then
        coordCiudad (Target.Row)
        LocalizaCiudad Target.Row - 2
        MuestraCiudad Target.Row - 2
    End If
End If

The routine coordCiudad calls CoordWebQuery that connects to the website of the city chosen by reading hyperlink in column A of the Cities sheet and save the coordinates of the city on the Coord sheet.

If the city is not in the list of cities www.timeanddate.com page, call the GetLatLonCiudad (city, country) function that refers to:
http://api.wunderground.com/auto/wui/geo/GeoLookupXML/index.xml?query=" & city & "," &country

For example, to obtain the coordinates of my city Zaragoza in Spain:
http://api.wunderground.com/auto/wui/geo/GeoLookupXML/index.xml?query=zaragoza,spain

The result was stripped from special signs with ConvertAccent(result), obtaining the latitude and longitude from an XML document type "MSXML2.DOMDocument"

The treatment of the coordinates is done in columns:
AB - Latitude in degrees and minutes.
AC - Longitude in degrees and minutes.
AD - Latitude decimal = decLat.
AE - Longitude decimal = decLong.
P - X location on the map = (decLong + 180) * 760 / 360-8
Q - Y location on the map = 240 - (decLat - 8) * 360 / 170

These latter formulas are approximate and depend on the size of the map.

And just need to locate and display the city on the map with MuestraCiudad and LocalizaCiudad functions, respectively, but I won't explain that because I like to make you wonder.

Traducción al español aquí.

Geocodificación del mapa de relojes

Al mostrar en el anterior artículo Cómo generar interfaces gráficos dinámicamente se ve un mapa mundial con las zonas horarias iluminadas por el Sol en tiempo real en el que se crean ciudades mediante geocodificación.

Esta técnica se emplea en los Sistemas de Información Geográfica - SIG para incluir las ciudades en una capa dentro de una Base de datos espacial en la que los puntos son las coordenadas geográficas terrestres medidas por latitud y longitud.

No se trata de competir con las múltiples aplicaciones de software SIG que hay en el mercado, pero si se puede decir que usar Excel como herramienta de geoetiquetado es más barato y sencillo y permite agregar información geográfica en los metadatos de imágenes que sirven para su georreferenciación.

La aportación de este Mapa de Zonas Horarias con Relojes Mundiales se puede catalogar dentro del movimiento de Información Geográfica Voluntaria o Participativa (VGI en su acrónimo inglés) difundiendo información geográfica proporcionada voluntariamente por personas, como el mapa de relojes que se puede ver en este video:

Geolocalización de ciudades

Veamos cómo se geolocalizan las ciudades sobre el mapa mundial.

Lo primero es obtener una lista de las ciudades y para ello se ha creado una conexión llamada Wordlclock desde la página Web:
http://www.timeanddate.com/worldclock/full.html?sort=0

Hoja Ciudades

En la hoja Ciudades se obtiene en las columnas:
A - El nombre de los países, provincias y ciudades separado por guiones.
B - Las horas locales de cada ciudad.
C - Las Zonas Horarias.

Mediante técnicas de separación o desconcatenación, a partir de los datos de la columna A se obtienen los datos de las columnas:
D - Los países.
E - Las provincias.
F - El nombre de las ciudades.

Con el Administrador de nombres se ha definido la lista de ciudades con:
ciudades=Ciudades!$F$3:$F$700

Hoja Coord

En esta hoja se almacenan temporalmente las coordenadas de las ciudades como:
Latitude: 38° 30' North
Longitude: 28° 00' West

Siendo las celdas:
B1 - Latitud en grados y minutos.
C1 - North o South.
B2 - Longitud en grados y minutos.
C2 - East o West.

Hoja Relojes

En la hoja Relojes se despliegan dos listas generadas mediante validación de datos en las columnas:
R - Para seleccionar una ciudad.
S - Para seleccionar un país.

Cuando cambia alguna celda de esta hoja se produce el evento Change y se ejecuta la rutina:
Private Sub Worksheet_Change(ByVal Target As Range)

En caso de cambiar una ciudad o país en las columnas R o S se llama a:

If ActiveSheet.Name = "Relojes" Then
    If Not Application.Intersect(Target, Range("R:S")) Is Nothing Then
        coordCiudad (Target.Row)
        LocalizaCiudad Target.Row - 2
        MuestraCiudad Target.Row - 2
    End If
End If

La rutina coordCiudad llama a CoordWebQuery que se conecta a la página Web de la ciudad elegida leyendo el hipervínculo en la columna A de la hoja Ciudades y guarda las coordenadas de la ciudad en la hoja Coord.

Si la ciudad no está en la lista de ciudades de la página www.timeanddate.com, se llama a la función GetLatLonCiudad(ciudad, pais) que consulta a:

http://api.wunderground.com/auto/wui/geo/GeoLookupXML/index.xml?query=" & ciudad & "," &pais

Por ejemplo, para obtener las coordenadas de mi ciudad Zaragoza en España:
http://api.wunderground.com/auto/wui/geo/GeoLookupXML/index.xml?query=zaragoza,spain

Al resultado se le quitan los signos especiales con ConvertAccent(result), obteniendo la latitud y la longitud desde un documento XML del tipo "MSXML2.DOMDocument"

El tratamiento de las coordenadas se hace en las columnas:
AB - Latitud en grados y minutos.
AC - Longitud en grados y minutos.
AD - Latitud decimal = decLat.
AE - Longitud decimal = decLong.
P - Localización X en el mapa = (decLong + 180) * 760 / 360 - 8
Q - Localización Y en el mapa = 240 - (decLat - 8) * 360 / 170

Estas últimas fórmulas son aproximadas y dependen del tamaño del mapa.

Ya sólo falta localizar y mostrar la ciudad en el mapa con las funciones LocalizaCiudad y MuestraCiudad, respectivamente, pero eso ¡ya no lo explico!

English translation of this post here.