🔝To translate this blog post to your language, select it in the top left
Google box.
Calendario Laboral (Continuación)
En el anterior artículo presenté un calendario laboral con festivos hecho
con una tabla dinámica. Lo puedes ver en este enlace: Calendario Laboral Dinámico en Excel
Ahora voy a hacer que los días festivos sean dinámicos, para no tener que
introducirlos manualmente cada año. Lo consigo usando fórmulas, que dependen
del año elegido, en lugar de fechas estáticas.
Este calendario no contiene macros VBA, únicamente contiene fórmulas y una
tabla dinámica. Puedes descargarlo al final de este artículo.
El único cambio necesario se ha hecho en la tabla de la hoja de
Eventos, a la que se han añadido varias columnas:
A - Fechas: con las fechas de los eventos festivos. Si un festivo cae en
domingo se traslada al lunes.
B - Fórmulas: con las fórmulas que calculan los días festivos. Luego las
explicaremos detenidamente.
C - Tipos de festivos: Nacionales; Regionales y Locales.
D - Día D del festivo.
E - Mes del festivo.
F - Descripción del evento festivo.
En color amarillo he
incluido los festivos de la ciudad de Zaragoza, Comunidad de Aragón, País
España.
En color naranja hay
algunos días que se celebran en los Estados Unidos de América - USA, ya que
su cálculo es especial. Sigue leyendo.
¿Qué fórmulas calculan los festivos dinámicamente?
Todas las fórmulas nuevas están en la tabla de Eventos.
Se ha definido un nombre para el año seleccionado del calendario:
En dos nuevas columnas se introduce el día inicial (columna D) y el mes
(columna E) de la fecha festiva, excepto para los días del Jueves Santo y
del Viernes Santo, para los que no se sabe anticipadamente su fecha.
La columna A calcula los días festivos a partir de la columna B con las
fórmulas y, si cae en domingo, se traslada el festivo al lunes con la
fórmula:
La columna B contiene las fórmulas que calculan los días festivos, para los
que he contemplado varios casos:
1) Festivos normales: por ejemplo el día de Año Nuevo con la
siguiente fórmula:
Son todos los festivos de España, excepto los días de Semana Santa que
son especiales.
2) Festivos en Semana Santa: el cálculo se hace con una fórmula muy
curiosa que expliqué en este artículo: Cómputos que hacen la "Pascua"
Cálculo del Jueves Santo:
Thomas Jansen
planteó esta curiosísima fórmula que funciona entre los años 1900 y 2203:
Esta fórmula es imposible de explicar y ni siquiera su autor lo hizo
cuando la publicó en una competición que finalizó el 31 de marzo de 1999
para obtener el Domingo de Pascua (enlace aquí)
Cálculo del Viernes Santo = un día más que el Jueves Santo: =B6+1
3) Festivos USA: el cálculo de los festivos de Estados Unidos de
América lo he incluido por sus características especiales. En esta imagen se
pueden ver las fórmulas de cálculo:
Para calcular estos días se usa la función FECHA con el año elegido, el mes
del festivo y para el día se calcula con las funciones ELEGIR y DIASEM, que
no voy a explicar aquí para que pienses un poco cómo se consiguen cumplir
las reglas de celebración de estos días festivos...
Descarga del calendario
Descarga este calendario con festivos dinámicos desde Google (con el botón
"Excel Download") o desde el enlace a Microsoft OneDrive:
Me he propuesto aprovechar el calendario de la tabla dinámica para crear un
calendario laboral, importando las fechas dinámicamente.
En la siguiente imagen se ve el aspecto de este calendario laboral, con una
segmentación para seleccionar el año, con los colores para distinguir los
días festivos nacionales, regionales y locales, con el día de hoy coloreado
en amarillo, con los fines de semana en rojo claro, con los 12 meses del año
elegido y con hasta 4 festivos visualizados por cada mes.
Voy a explicar las características más relevantes de este calendario con la
intención de que seas capaz por tí mismo de adaptarlo a tus necesidades o de
crear tu propio calendario, ¿te parece?
Copyright
Yo, Pedro Wave, estoy publicando bajo una licencia
Creative Commons License:
En este vídeo puedes seguir las explicaciones de cómo he creado este
calendario:
Si después de ver el vídeo no te ha quedado claro o prefieres las
explicaciones paso a paso, sigue leyendo.
Diseño Técnico
A continuación daré una explicación de cómo crear un calendario laboral a
partir de una tabla dinámica.
Características más relevantes del calendario laboral:
Admite un rango de años desde 2020 hasta 2050.
Muestra los 12 meses del año como 4 trimestres.
El calendario se puede imprimir.
Los festivos nacionales, regionales y locales están coloreados y se
describe cada día festivo.
El día de hoy está coloreado de amarillo, y se selecciona con un
hipervínculo.
Se pueden mostrar u ocultar los números de semana, siempre que la hoja
esté desprotegida. Las columnas con los números de semana están agrupadas:
Nivel 1: Oculta las columnas B; K y T.
Nivel 2: Muestra las columnas B; K y T.
Para diseñar este calendario hacen falta 4 hojas:
'Calendario_Laboral': con el calendario dinámico.
'Eventos': con la tabla de eventos, por ejemplo con los días
festivos.
'Calendario Dinámico': con la tabla dinámica en forma de
calendario. Se puede ocultar.
'Fechas': con la tabla de fecha que es el origen de datos de la
tabla dinámica. Se puede eliminar.
Todas estas hojas están protegidas sin contraseña.
Hoja 'Fechas'
En esta hoja está el origen de datos de la tabla dinámica, como una tabla de
11.324 filas, con fechas del 1 de enero de 2020 al 31 de diciembre de 2050.
Hacen falta 3 campos para la tabla de fechas:
Fecha: el primer día es el 1 de enero de 2020 y los demás se obtienen
añadiendo un uno al día anterior: =A2+1
Día de la semana: =DIASEM([@Fecha];2)
Número de semana: =NUM.DE.SEMANA([@Fecha];2)
El segundo argumento vale 2 para que los días de la semana vayan del 1-lunes
al 7-domingo.
Después de generar la tabla dinámica, esta hoja de fechas se puede eliminar
para reducir el tamaño del archivo y aumentar su rendimiento.
Hoja 'Calendario Dinámico'
En esta hoja se ha creado un calendario a partir de una única tabla
dinámica, con este aspecto:
Si quieres saber cómo crear este calendario dinámico debes leer este
artículo de mi blog:
TRUCO:
La segmentación de datos de "Años" sólo permite seleccionar un único año.
Si se selecciona más de un año, salta un mensaje de advertencia:
"No se puede cambiar parte de una celda combinada."
Para continuar presiona el botón: Aceptar
Se han combinado celdas a propósito en la fila 94 para:
"NO PASAR DE ESTA FILA - Seleccionar un único año"
TRUCO:
Con estas celdas combinadas se consigue que no pueda crecer la tabla
dinámica y, por lo tanto, no se pueda seleccionar más de un año…
ATENCION:
La hoja 'Calendario Dinámico' se puede dejar oculta por ser auxiliar
para calcular los datos del calendario laboral.
Hoja 'Eventos'
Esta hoja contiene la tabla de eventos con 3 campos:
Fechas: Con los días festivos.
Tipos de fechas: Nacionales; Regionales y Locales.
Eventos: Descripción del festivo.
Como ejemplo se han incluido los festivos de 2020 y 2021 para la ciudad de
Zaragoza, Aragón, España.
Los eventos festivos pueden ser manualmente editados, añadidos y eliminados
para incluir los festivos de tu pueblo o ciudad hasta el año 2050.
Hoja 'Calendario_Laboral'
El año del calendario se elige con la segmentación de datos de "Años".
La segmentación en 2 filas permite seleccionar un año del 2020 al 2050.
Como esta segmentación de años está conectada con la tabla dinámica que
hemos visto antes, si seleccionas más de un año salta la advertencia:
"No se puede cambiar parte de una celda combinada."
Para continuar presiona el botón: Aceptar
Los nombres de los meses (con formato de celda: mmmm) se obtienen para el
año 2000 (fuera del rango de años del calendario) con las fórmulas:
enero: =FECHA(2000;1;1)
resto de meses: =FIN.MES(B7;0)+1
Los nombres de los días de la semana se obtienen de la tabla dinámica con
fórmulas del tipo: ='Calendario Dinámico'!C$3
Los números de semana se obtienen así:
La primera semana de enero: =1
La primera semana de febrero a diciembre es la penúltima semana del mes
anterior, si no está completa, y es la última semana del mes anterior si
la penúltima está completa: =SI(CONTAR.SI(C13:I13;0)>0;B13;B14)
Para otras semanas se añade un 1.
ATENCION:
Los días (formato de celda: d) se obtienen con la función para importar
datos de la tabla dinámica:
El mes se obtiene de la cabecera con el nombre del mes. En este caso de la
celda $B$7
El día de la semana se obtiene de la fila con los nombres de los días, de
"lun" (C$8) a "dom" (I$8).
El número de semana se obtiene de la columna $B y filas 9 a 14.
El año se obtiene de la celda $K$2 con el año elegido.
IMPORTANTE: Todas las fechas son fechas de Excel con sus números de serie. Que
sean fechas de Excel ayuda a buscarlas en la tabla de festivos para
aplicarles formatos condicionales.
En el menú: Fórmulas - Administrador de Nombres
se pueden analizar los nombres definidos que se usan en las fórmulas de este
calendario.
Comienzan por "Rango_"
Con 3 nombres se definen las columnas de la tabla de eventos: TablaEventos
Rango_Hoy llama a la función: =HOY()
Debajo de cada mes hay hasta 4 días festivos y su descripción, gracias a una
fórmula matricial oculta (con formato: ;;;) en las columnas con los números
de semana.
Las fórmulas matriciales se introducen presionando a la vez las teclas:
Control + Mayúsculas + Intro
Fin de semana (anaranjado claro): =Y(DIASEM(B7;2)>5;B7>40000)
Hoy (amarillo): =Y(B7=Rango_Hoy;B7>40000)
Con 3 formatos condicionales se colorean los tipos de días festivos. La
fórmula es:
=Y(B7>40000;SI.ERROR(INDICE(Rango_Tipos;COINCIDIR(B7;Rango_Fechas;0));"")=INDICE(Rango_Tipos_Fechas;n;1))
Siendo n:
1 - Nacionales: verde.
2 - Regionales: azul.
3 - Locales: púrpura.
Las fórmulas se aplican a valores de celda >40000, por lo que no se
aplica formato condicional a fechas anteriores al año 2010. Como se han
usado fechas del 2000 para los nombres de los meses, éstos no cambian de
color con el formato condicional.
NOTA:
Se pueden cambiar estas celdas:
B2: Calendario Laboral, con el nombre que quieras, por ejemplo: Calendario
Fútbol
T2: Zaragoza, con el nombre de tu pueblo o ciudad.
En estas 4 celdas se definen los tipos de días:
Z63: Festivos
Z64: Nacionales
Z65: Regionales
Z66: Locales
BONUS:
Si quieres que sea un calendario con los partidos de fútbol de tu equipo
favorito, edita las celdas:
Z63: Fútbol
Z64: La Liga
Z65: Champions
Z66: Copa del Rey
Y edita la hoja 'Eventos' con estos 3 tipos de fecha.
Agradecimiento
Para hacer este calendario me he inspirado en las siguientes páginas, a
cuyos autores les estoy muy agradecido:
He buscado en la Web algún calendario basado en tablas dinámicas y no he
encontrado casi nada. El calendario más parecido que he visto ha sido el del
siguiente enlace, pero necesita demasiados campos en la tabla de origen de
datos para generar la tabla dinámica.
Me he propuesto hacerlo con sólo 3 campos como origen de datos y que sea la
tabla dinámica la que elabore el calendario, siendo éste el aspecto del
nuevo calendario dinámico:
Con dos segmentaciones de datos, una para los años y otra para los meses, se
puede configurar dinámicamente el calendario perpetuo, abarcando los siglos
XX y XXI del calendario gregoriano.
En este vídeo puedes seguir paso a paso las explicaciones de cómo crear el
calendario dinámico:
Aumenta el volumen para escuchar las explicaciones, pues la grabación de
audio está a un nivel muy bajo. Disculpa las molestias. Para ayudar a seguir
la explicación paso a paso he usado un cuadro de texto que muestra cada uno
de los pasos, lo que espero sirva de ayuda para que puedas reproducir las
explicaciones y consigas hacer tú mismo el calendario con una tabla
dinámica, que es lo que pretendo con este artículo y el vídeo que le
acompaña.
Paso a paso de cómo crear el calendario
En una hoja llamada 'Fechas' se crea una tabla con 3 columnas: Fecha; Día
Semana; Núm. Semana.
Hay que rellenar la columna de Fecha con una secuencia desde el 01-01-1901,
día de comienzo del siglo XX. El año 1900 es bisiesto erróneamente en
Excel, por lo que no será incluido en el calendario.
Una forma elegante de rellenar las fechas es usando la función:
Comienza en una nueva hoja, cambiando su nombre a: Fechas
Ponla en color amarillo.
Edita la tabla de fechas:
En A1 escribe la cabecera: Fecha; Día Semana; Núm. Semana
En A2 pon el día 01-01-1901
En A3 escribe la fórmula: =A2+1
En el menú Vista: Desmarca la Línea de cuadrícula
Inmoviliza la fila superior
Inserta la tabla con cabecera: Edita su nombre: TablaFechas
Cambia su tamaño a: 73050 filas
Cambia su formato a: Fecha corta
ATENCIÓN: Comprueba que se ha rellenado hasta el último día: 31-12-2100
Calcula el día de la semana en B2: =DIASEM([@Fecha];2)
NOTA: El segundo argumento indica con un 2 que la semana comienza en lunes
(1) y acaba en domingo (7).
Calcula el número de semana en C2: =NUM.DE.SEMANA([@Fecha];2)
NOTA: El segundo argumento indica con un 2 que la semana comienza en
lunes.
Copia las celdas B2 y C2 hacia abajo con autorrelleno.
Centra y ajusta las columnas.
Copia las fórmulas dinámicas de la tabla como valores estáticos.
NOTA: Este paso es recomendable si no va a crecer esta tabla, ya que
mejora su rendimiento al no tener que recalcularla.
Inserta la tabla dinámica en una nueva hoja a partir de esta tabla.
Cambia el nombre de la hoja a: Calendario
Ponla en color azul.
Selecciona todas las celdas (arriba a la izquierda de las filas y
columnas)
Selecciona menú de Inicio: Alineación
Centrar y Alinear en el medio
Selecciona "Fecha" como: Etiqueta de fila
Agrupa las fechas por: Meses y Años.
Selecciona "Núm. Semana" como: Etiqueta de fila
Selecciona "Día Semana" como: Etiqueta de columna
Selecciona "Fecha" como: Resumen de Valores
En Valores: Cuenta de Fecha Cambia la Configuración de campo de valor…
Resume el campo de valor por: Máx.
ATENCIÓN: Este es el truco principal de este calendario.
Valores como: Máx. de Fecha
¡¡¡ Y se ven los números de serie de las fechas en Excel !!!
En el menú Vista: Desmarca la Línea de cuadrícula
Selecciona la fila 4: Inmoviliza paneles
En Herramientas de tabla dinámica: Diseño
Totales generales: Desactivar para filas y columnas
¡Ya tenemos el esqueleto del calendario montado con una tabla dinámica!
Cambia los nombres de las etiquetas:
B2: Máx. de Fecha por: Calendario
B3: Etiquetas de fila por: Año / Mes / Semana
C2: Etiquetas de columna por: Día
C3 a I3: Cambia 1 por lun; … ; 7 por dom
Selecciona la celda B4 (1901):
Haz clic con el botón derecho del ratón y aparece el menú contextual.
Desmarca: Subtotal "Años"
Selecciona la celda B5 (ene):
Haz clic con el botón derecho del ratón y aparece el menú contextual.
Desmarca: Subtotal "Fecha"
Selecciona la celda D6 con el primer día del calendario:
Haz clic con el botón derecho del ratón y aparece el menú contextual.
Selecciona: Configuración de campo…
Haz clic en el botón de abajo a la izquierda: Formato de número
Selecciona Categoría: Personalizada
Cambia el Tipo: Estándar por la letra: d
¡Perfecto! ¡Has conseguido que los valores de fechas se muestren como los
días del mes!
Vamos a conseguir que el calendario sea dinámico.
En Herramientas de tabla dinámica: Opciones
Inserta Segmentación de datos
Marca: Años y Fecha
Selecciona la segmentación de datos: Fecha
En Herramientas de Segmentación de datos: Opciones
Cambia el Título de Fecha por: Meses
Cambia el número de columnas a 12
Sitúa esta segmentación arriba
Amplia su anchura hasta que se vean los nombres de los meses.
En Herramientas de tabla dinámica: Opciones
Desmarca: Botones +/-
Selecciona una celda de la tabla dinámica:
Haz clic con el botón derecho del ratón y aparece el menú contextual.
Selecciona: Opciones de tabla dinámica
Desmarca: Autoajustar anchos de columna al actualizar
Selecciona el rango de columnas C:I
Haz clic con el botón derecho del ratón para mostrar el menú contextual
Selecciona: Ancho de columna…
Pon un valor de: 8
En Herramientas de tabla dinámica: Diseño
Selecciona un estilo a tu gusto…
BONUS: Aplicar formato condicional a los días de entresemana:
Selecciona el rango de celdas: =$C$4:$I$15094
En el menú Inicio: Formato condicional
Haz clic en: Nueva regla
Utiliza la fórmula: =DIASEM(C4;2) 6
Formato: Color azul claro
Haz clic en el botón: Aceptar
BONUS: Aplicar formato condicional a los sábados y domingos (findes):
Selecciona el rango de celdas: =$C$4:$I$15094
En el menú Inicio: Formato condicional
Haz clic en: Nueva regla
Utiliza la fórmula: =Y(DIASEM(C4;2)>5;C4<>"")
Formato: Color rojo claro
Haz clic en el botón: Aceptar"
EXTRAS: Aplicar formato condicional al día de hoy:
Selecciona el rango de celdas: =$C$4:$I$15094
En el menú Inicio: Formato condicional
Haz clic en: Nueva regla
Utiliza la fórmula: =C4=HOY()
Formato: Color amarillo
Haz clic en el botón: Aceptar
Selecciona la celda B5 (ene):
Haz clic con el botón derecho del ratón y aparece el menú contextual.
Selecciona: Configuración de campo...
Selecciona la pestaña: Diseño e impresión
Marca: Insertar línea en blanco después de cada etiqueta de elemento
Con las segmentaciones de datos:
Selecciona el año: 2020
Selecciona 3 meses: de oct a dic
ATENCIÓN: Cómo reducir el tamaño del libro:
Selecciona la fila 27.
Mantén pulsada la tecla: Mayúsculas
Presiona una vez la tecla: Fin
Presiona una vez la tecla de cursor: Flecha hacia abajo
Haz clic en cualquier fila con el botón derecho del ratón
Haz clic en: Eliminar
IMPORTANTE: Guarda el libro inmediatamente.
IMPORTANTE: Elimina la hoja Fechas si no vas a ampliar el calendario a más
siglos que el XX y el XXI. La tabla dinámica seguirá igual de dinámica y
se reducirá el tamaño del libro y, por lo tanto, los tiempos para abrirlo
y recalcularlo…
¡¡¡ OBJETIVO CONSEGUIDO !!!
Si has seguido todos los pasos anteriores o has seguido las explicaciones
del vídeo,
¡¡¡ ya tendrás un calendario dentro de una tabla dinámica !!!
Si no has seguido los pasos, o no lo has conseguido, puedes descargar esta
plantilla desde Google (con el botón "Excel Download") o desde el enlace a
Microsoft OneDrive, para jugar con este calendario:
Esta no es la tercera parte de la trilogía que estoy escribiendo sobre fechas anteriores al año 1900, sino que es un inciso para presentar un nuevo
Calendario Perpetuo
con fechas desde el año 1900, y que tiene como valor añadido la
posibilidad de mostrar los eventos o efemérides de cada día pasando el
ratón sobre las celdas. Accede al siguiente enlace en inglés para saber
cómo:
La tercera entrega de la trilogía se basará en este calendario pero usando
fechas en VBA, por lo que abarcará fechas desde el 1 de enero del año 100,
con lo que servirá para todo el
Calendario Gregoriano.
Este es el aspecto del nuevo calendario perpetuo:
1) Características relevantes
Características relevantes del Calendario Perpetuo en Excel:
Calendario Perpetuo Gregoriano con un rango de fechas del 01-03-1900 al
31-12-9999.
Vista de 3 meses: el mes seleccionado; el anterior y el posterior.
El primer día de la semana puede ser domingo o lunes.
Muestra u oculta los días de otros meses.
Sábados y domingos marcados en color rojo.
Flechas para incrementar o decrementar el mes, rotando como un carrusel.
Flechas para cambiar el año.
Selección de un mes.
Selección de una año mediante 2 cifras para las centenas y 2 cifras para
las unidades.
Si se decrementa el año 1900 pasa al año 9999.
Si se incrementa el año 9999 pasa al año 1900.
Selección del día de hoy.
Selección del primer día del calendario perpetuo: 01-03-1900.
Selección del último día del calendario perpetuo: 31-12-9999.
Muestra u oculta un calendario auxiliar y otros datos.
Tabla de fechas con las efemérides guardadas, por ejemplo los Días de
Independencia de los países obtenidos de Wikipedia (enlace aquí).
Hasta 12 efemérides por día en 3 páginas con 4 eventos cada una.
BONUS: Al pasar el cursor del ratón sobre una celda muestra los eventos de
ese día.
Activa o desactiva el evento del ratón sobre una celda.
Botones de animación con avance y retroceso de los meses y con 5
velocidades de animación.
Botones para ver los primeros o los últimos 3 meses del año.
Botones para incrementar o decrementar de 3 en 3 meses.
Filtro de un día en la tabla de fechas.
No voy a explicar cómo usar este calendario, lo interesante es probar cada
una de las características y experimentar con el diseño de la experiencia
del usuario (UXD - User eXperience Design) de este calendario perpetuo.
Si tienes sugerencias para mejorar este calendario, puedes compartirlas
escribiendo un comentario al final de este artículo.
2) Plantilla del Calendario Perpetuo desde 1900
Descarga la plantilla totalmente gratuita, con las macros visibles y las
hojas protegidas sin contraseña, desde Google (con el botón "Excel
Download") o desde el enlace a Microsoft OneDrive:
Año>=1900 - Donde se muestra y se controla el calendario.
Fechas - Donde se guarda la tabla de fechas.
Idiomas - Donde se guardan las traducciones de los idiomas.
En la hoja 'Idiomas' se selecciona en la celda A1 entre 2 idiomas:
Español o English. Los textos de la columna A son las traducciones que aparecen en el
calendario. Si quieres, puedes añadir más columnas con más idiomas. Una
macro cambia los textos de la cabecera de la tabla de la hoja
'Fechas'.
La hoja 'Fechas' contiene la tabla de fechas en 3 columnas:
Fecha; Evento y Núm. Serie VBA. Esta última columna es calculada con una macro y sirve para ordenar las
fechas por su número de serie en VBA, o sea, con números positivos y
negativos. He incluido algunas fechas significativas del calendario
gregoriano desde 1900 y las efemérides de los Días de Independencia de
varios países, obtenidos de la
Wikipedia - enlace aquí. Desprotegiendo la hoja sin contraseña se puede editar cualquier fecha.
El calendario perpetuo está en la hoja 'Año>=1900'. Para
explicar cómo ha sido diseñado nos centraremos en el mes central del
calendario, en el rango K5:Q12
En la celda L5 se muestra el nombre del mes pero contiene el día 1 del mes
y año seleccionados con alguno de los controles del calendario: con el
desplegable de la propia celda; con el cambio de año; con las flechas de
incremento o decremento de los meses; con las teclas de animación de la
fila 14.
En el rango X34:Z46 está la tabla auxiliar con la lista de meses que se
carga con la validación de datos de la celda L5.
El cálculo de un mes del calendario comienza en el calendario auxiliar que
se encuentra entre las filas 23 y 30 (se puede ver haciendo clic en la
fila 21).
En las celdas B25, K25 y T25 se calcula el primer día de la semana en que
comienza un mes, con las fórmulas:
Rango_DíaSemana: ='Año>=1900'!$Z$2 (La semana comienza en:
1-dom; 2-lun)
Esas celdas contienen el número de serie de una fecha, por lo que el resto
de los días se calculan a partir de esas celdas sumando un uno al día
anterior. Se calculan 6 semanas para cubrir todo el mes. El formato de las
celdas de esas fechas es una letra "d", por lo que se muestra el número
del día. En los meses de las filas 23 a 30 se ven siempre los días de
meses anteriores. Los días con eventos o efemérides en la hoja 'Fechas' se
marcan en color naranja gracias al formato condicional. Los sábados y
domingos se pintan en color rojo.
¡¡¡ Y ahora el truco fundamental !!!
¿Qué fórmula produce el efecto de pasar el ratón por encima de la
celda ejecutando una macro?
Vamos a analizar una fórmula posible para la celda K8:
=HIPERVINCULO(MouseOver(K26);DIA(K26))
Hace referencia a la celda K26 del calendario auxiliar, que contiene una
fecha cualquiera y llama a la
función HIPERVINCULO
que tiene 2 argumentos.
El segundo argumento es un nombre descriptivo que, en este caso, llama a
la función DIA para mostrar el número del día en la celda.
El primer argumento es la ubicación del hipervínculo, ejecutando la macro
MouseOver(K26), con la celda de la fecha auxiliar como
argumento.
Esa era la fórmula original que sirve únicamente para días dentro del mes
del calendario. La fórmula definitiva en la celda K8 es un poco más
compleja:
Esta fórmula sirve para todos los días del mes y tiene en cuenta si se
muestran los días de otros meses (controlado por la celda Z3) y si hay
errores en las fechas, cosa que sólo ocurre en enero de 1900 y en
diciembre de 9999.
La macro MouseOver está en el módulo ModPasarRatónSobreCeldas
La función MouseOver devuelve una String con la ubicación de la
propia celda en que se llamó, pero antes modifica la celda B16
("Rango_Día") con la fecha del día sobre el que ha pasado el ratón por
encima. Este efecto lo publicó por primera vez Jordan Goldmeier en
su blog
OPTION EXPLICIT VBA
por lo que le estoy muy agradecido, pues ha contribuido a enriquecer la
interactividad y usabilidad de Excel. En el siguiente enlace hay un
ejemplo mío de lo que se puede llegar a hacer con este excelente efecto de
pasar el ratón sobre las celdas de Excel sin tener que hacer clic en
ellas:
Las fórmulas que llaman a la función HIPERVINCULO consiguen cambiar el día
en la celda B16, denominada "Rango_Día",con la macro MouseOver:
Range("Rango_Día").Value2 =
rCelda.Value2
¡MENUDO TRUCO!
En las filas 17 a 19 se muestran los eventos o las efemérides del día de 4
en 4 y hasta en 3 páginas, obtenidas de la tabla auxiliar en el rango
B34:V46
La columna "Fila" contiene una fórmula matricial (introducida con las
teclas: Control + Mayúsculas + Intro) para obtener 12 filas con los
eventos del día extraidos de la tabla de la hoja 'Fechas':
Haciendo clic en el día de la celda B16, si hay eventos en ese día, los
filtra en la hoja 'Fechas'.
4) Próximos pasos
En una próxima entrega publicaré un calendario perpetuo similar pero
usando las fechas de VBA en lugar de las de Excel, con lo que se podrán
programar eventos desde el día 1 de enero del año 100.
Si te gustan los calendarios puedes leer todos los calendarios que he
publicado hasta la fecha en este enlace:
En esta entrada explicaré cómo calcular la edad y el día de la semana y a
restar fechas anteriores al año 1900:
1) Formatos de celdas con fechas
2) Día de la semana en Excel y en VBA
3) Calcular diferencia de fechas en días
4) Calcular la edad en años
5) Plantilla con fechas Excel y VBA
1) Formatos de celdas con fechas
En las celdas de Excel se permiten fechas desde el 1 de enero de 1900
aunque, como ya sabemos por el primer artículo de esta trilogía, la primera
fecha válida es el 1 de marzo de 1900.
La competencia se ha adelantado a Excel, pues permite fechas con números de
serie negativos en sus celdas:
LibreOffice Calc permite fechas desde el día 15 de octubre de
1582, que es el primer día del Calendario Gregoriano.
Google Sheets permite las fechas del Calendario Gregoriano y
desde el 1 de enero de -1, que deberían ser convertidas al Calendario
Juliano.
Excel obliga a usar macros en VBA para poder tratar los números de serie
negativos, como variables tipo Date en VBA, como se comentó en el primer artículo de esta trilogía, y
es lo que vamos a hacer a continuación.
Para saber más sobre fechas en Excel puedes leer estos dos artículos en
inglés:
Ya sabemos todos que para obtener el nombre del día de la semana en Excel, a
partir de una celda con una fecha, la fórmula apropiada es:
=TEXTO(fecha;"dddd")
Pero esta fórmula sólo funciona para fechas a partir del 1 de marzo de
1900.
En el archivo (a descargar en el apartado 5) he preparado un ejercicio con
las fechas en Excel y VBA que me sirvió para ayudar a responder sobre este
asunto en el foro de
ayudaexcel.com:
Un usuario necesitaba saber el día de la semana (lunes, martes,
miércoles...) de fechas anteriores a 1900, para lo que preparé un archivo
con 3 macros en un módulo VBA.
La función ObtenerDíaSemanaISO()
devuelve el día de la semana, si se le pasa una cadena de texto en formato
ISO 8601, llamando a las funciones
ObtenerNúmDíaSemanaISO() y ObtenerSerieFechaISO()
Es fácil convertir una string con una fecha en formato de texto al formato
estándar ISO, lo que se puede ver en la hoja 'Fechas' del archivo que se
puede descargar en el apartado 5) de esta entrada.
Si la fecha está en formato fecha, o en formato fecha sin convertir a ISO
8601, se deben usar las siguientes funciones:
Para obtener el número de serie de una fecha:
=ObtenerSerieFechaVBA($A2;ESNUMERO($A2))
Para obtener el día de la semana de una fecha:
=ObtenerDíaSemanaVBA($A2;ESNUMERO($A2))
Los cálculos están en la hoja 'Día Semana':
Como el año 1900 no fue bisiesto, devuelve error para el 29 de febrero.
3) Calcular diferencia de fechas en días
Para calcular la diferencia entre dos fechas en días se puede usar una
función no documentada, herencia de Lotus 123, que se puede consultar en el
siguiente enlace:
La función SIFECHA, en inglés DATEDIF, permite calcular la
diferencia en días, semanas, meses y años entre dos fechas. Por ejemplo,
para calcular los días:
=SIFECHA("1-01-2014";"6-05-2016";"d")
El tercer argumento indica, con la letra "d", que la diferencia está
expresada en días.
La forma más fácil de obtener los días es restar las fechas:
=ENTERO(Fecha1 - Fecha2)
De cualquiera de las maneras, si intentamos calcular los días para una o
dos fechas anteriores al año 1900, nos dará error de valor: #¡VALOR!
pues Excel no funciona con números de serie de fecha negativos.
Para años anteriores a 1900 se deben usar macros en VBA con la función:
= DateDiff("d", dtFecha1, dtFecha2)
Y eso se ha hecho en el archivo descargable, en el módulo
"ModRestarFechas1900":
Function RestarFechas(sFecha1 As String, sFecha2 As String) As Long
que usa las funciones DateValue y DateDiff para calcular el
número de días entre fechas desde el 1 de enero del año 100 hasta el 31 de
diciembre de 9999, como se puede ver en la hoja 'Edad':
En color de fondo naranja, las fechas con dos cifras para el año, que se
tranforma en los años 1930 y 1980. En color amarillo la fecha actual con la
función: =HOY()
Si "Fecha Final" está vacía, toma esa fecha como la actual.
La columna con el cálculo de la Edad se explica en el siguiente apartado.
4) Calcular la edad en años
El cálculo de la edad en años se puede hacer con la siguiente fórmula para
dos celdas de Excel:
=ENTERO(FRAC.AÑO(A1;B1))
Otra fórmula es usar el tercer argumento para años "y" de la función no
documentada que ya conocemos:
=SIFECHA(A1;B1;"y")
Pero seguimos teniendo el problema de que las fechas en las celdas sólo
sirven para números de serie positivos de fechas.
Para años anteriores a 1900 se deben usar macros en VBA con la función:
= DateDiff("yyyy", dtFecha1, dtFecha2)
Y eso se ha hecho en el archivo descargable, en el módulo
"ModRestarFechas1900":
Function ObtenerEdad(sFechaNacimiento As String, sFechaCálculo
As String) As Long
que usa las
funciones DateValue y DateDiff para
calcular el número de años entre fechas, desde el 1 de enero del año 100
hasta el 31 de diciembre de 9999, como se puede ver en la hoja
'Edad'.
Esta función me sirvió para ayudar en el foro de ayudaexcel.com:
Descarga la plantilla totalmente gratuita, con las macros visibles y las
hojas protegidas sin contraseña, desde Google (con el botón "Excel
Download") o desde el enlace a Microsoft OneDrive:
He votado y comentado la siguiente sugerencia "Dates prior to 1900"
en Excel UserVoice para poder usar fechas con número de serie negativo en las celdas. La
sugerencia lleva desde el 27 de septiembre de 2015. En 5 años Microsoft
Excel no ha dicho ni pío, cuando la competencia de Google Sheets y
LibreOffice Calc funcionan perfectamente con todo el Calendario Gregoriano.
¿Me ayudas a que esta sugerencia sea popular, votando también?
Actualización 2022-06-06: Esa página provoca el Error 404: Page Not Found.
Microsoft ha borrado intencionadamente UserVoice, y ha desaparecido
todo el historial de sugerencias enviadas por los usuarios de Office, con lo
que se han perdido todos los votos de los últimos años.
Desde hace 7 meses Microsoft ha creado un nuevo portal de Feedback (en inglés), donde compartir comentarios y ayudar a hacer mejoras y
crear nuevos productos, con lo que he vuelto a escribir un comentario y
votar por esta sugerencia:
🔝To translate this blog post to your language, select it in the top left
Google box.
Desde que me dedico en serio a los cálculos me ha apasionado la
conversión de fechas, con sus días y sus horas.
Esta entrada del blog es la primera parte de una trilogía sobre el cálculo
de fechas anteriores al año 1900 con funciones en VBA. La trilogía
explicará:
En esta entrada explicaré lo que interesa conocer sobre las fechas en Excel
y en VBA para poder calcular correctamente las fechas anteriores y
posteriores al año 1900:
1) Fechas correctas y erróneas en Excel
2) Fechas correctas y erróneas en VBA
3) Calendario Gregoriano
4) Fechas según norma ISO 8601
5) Listado de fechas curiosas
6) Plantilla con fechas Excel y VBA
En esta imagen se puede ver cómo se pretenden calcular las fechas anteriores
y posteriores al 1 de marzo de 1900, tanto con fórmulas en Excel como con
macros en VBA.
Esta tabla está en la hoja 'Fechas' de la plantilla que se puede
descargar al final de esta entrada y donde se puede obtener la fórmula que
convierte las fechas en formato ISO 8601. Los números de serie y días
de la semana de enero y febrero de 1900 con calculados incorrectamente por
Excel, lo que se explica si lees más abajo.
1) Fechas correctas y erróneas en Excel
Excel es una herramienta invaluable para calcular fechas, eso sí, para días
desde el 1 de marzo de 1900 hasta el 31 de diciembre de 9999.
En una celda de Excel las fechas se guardan como la parte entera positiva de
un número decimal y se define como el número de serie de un día, comenzando
en el número de serie 1, que corresponde con el día 1 de enero de 1900, que
es incorrectamente calculado por Excel.
Los meses de enero y febrero de 1900 no se deben usar en los cálculos, pues
hay un error conocido en Excel para esos dos meses, ya que se definió
erróneamente 1900 como año bisiesto, por lo que el primer día correcto para
usarlo en los cálculos de fechas en las celdas, fórmulas y funciones de
Excel es el 1 de marzo de 1900, con el número de serie 61.
Ver el siguiente enlace donde se explica el error cometido por los
desarrolladores de Excel:
En esta tabla se muestran los números de serie admitidos por Excel para el
rango de fechas correctas:
Las horas se guardan en la parte decimal del número. De las horas no hablaré
en esta entrada por lo que, si quieres saber más sobre las horas, abre el
siguiente enlace:
Para calcular fechas anteriores al 1 de marzo de 1900 vienen en nuestra
ayuda las funciones de fecha de VBA, ya que permiten cálculos con fechas
anteriores al 1 de marzo de 1900, gracias a que trabajan con números de
serie negativos para fechas anteriores al año 1900.
El primer número de serie negativo es el -1 que se corresponde con la fecha
del 29 de diciembre de 1899.
El último número de serie negativo es el -657.434 que se corresponde con la
fecha del 1 de enero del año 100.
El rango de fechas posible de un tipo de dato Date en VBA es desde el
1 de enero de 100 hasta el 31 de diciembre de 9999.
En esta tabla se muestran los números de serie admitidos por VBA para el
rango de fechas correctas:
Para calcular el número de serie en VBA he definido la siguiente función:
=ObtenerSerieFechaISO($B2)
Para calcular el nombre del día de la semana en VBA he definido la siguiente
función:
=ObtenerDíaSemanaISO($B2)
El formato de fecha en la celda B2 debe ser según norma ISO 8601,
explicada en el apartado 4).
3) Calendario Gregoriano
En este artículo en adelante siempre se representarán las fechas en el
Calendario Gregoriano (enlace aquí) ya que el Calendario Juliano (enlace aquí) prácticamente sólo lo usan los historiadores.
Para calcular según el Calendario Juliano se puede estudiar el siguiente
enlace:
Desde este momento voy a seguir la norma internacional ISO 8601
para representar las fechas en formato texto, cosa muy recomendable para
fechas anteriores al 1 de marzo de 1900. Este formato de fechas se puede
consultar en el siguiente enlace:
La norma ISO 8601 ayuda a eliminar las dudas que pueden surgir de las
diversas convenciones, culturas y zonas horarias de días y fechas que
afectan a una operación global. Ofrece una forma de presentar fechas y horas
claramente definidas y comprensibles tanto para las personas como para las
máquinas.
El formato de fechas ISO 8601 es así: AAAA-MM-DD
En inglés: YYYY-MM-DD
Las cifras están separadas por guiones "-" y las letras representan el año,
mes y día, rellenadas con ceros por la izquierda:
AAAA o YYYY: son las 4 cifras del año.
MM: son las 2 cifras del mes.
DD: son las 2 cifras de día.
Por ejemplo, el día 2000-02-29 fue el 29 de febrero de
2000.
5) Listado de fechas curiosas
La siguiente tabla está en la hoja 'Año<1900' de la plantilla que
se puede descargar al final de esta entrada.
Para no cometer errores, conocidos como bugs en inglés, en el cálculo de
fechas se deben tener en cuenta las siguientes fechas, según norma
ISO 8601:
9999-12-31 (Nº Serie: 2.958.465) es el último día correcto en Excel
y en VBA. A partir de ese día Excel y VBA dejarán de funcionar... (tal y
como los conocemos hoy en día).
1900-03-01 (Nº Serie: 61) es el primer día correcto, por lo que
puede ser tratado por las funciones de fecha y hora de Excel.
1900-02-29 (Nº Serie: 60 en Excel; 61 en VBA) es una fecha errónea
tanto en Excel como en VBA pues el año 1900 no fue bisiesto.
1900-01-01 (Nº Serie: 1 en Excel; 2 en VBA) es el primer día
erróneo en Excel y el segundo día correcto en VBA con un número de serie
positivo.
1899-12-31 (Nº Serie VBA: 1) es el primer número de serie positivo
en VBA. En Excel no existen números de serie negativos.
1899-12-30 (Nº Serie VBA: 0) es el número de serie cero en
VBA.
1899-12-29 (Nº Serie VBA: -1) es el primer número de serie
negativo en VBA.
1752-09-14 (Nº Serie VBA: -53.797) fue el primer día
Gregoriano en Inglaterra.
1582-12-20 (Nº Serie VBA: -115.792) fue el primer día
Gregoriano en Francia.
1582-10-15 (Nº Serie VBA: -115.858) fue el primer día
Gregoriano en España, decretado en Roma por el papa Gregorio XIII, que dio
nombre al Calendario Gregoriano.
0100-01-01 (Nº Serie VBA: -657.434) es el primer día
correcto para las funciones de fecha en VBA.
0099-12-31 Fecha interpretada por Excel y VBA por compatibilidad.
Cuando el año se representa con 2 cifras de la 30 a la 99 se interpreta
como los años 1930 a 1999.
0000-01-01 Fecha interpretada por Excel y VBA por compatibilidad.
Cuando el año se representa con 2 cifras de la 00 a la 29 se interpreta
como los años 2000 a 2029.
Con estas fechas ¿qué quiero decir?
¡Que no todo vale cuando calculamos con las funciones de fecha!
¡Tanto si hablamos de Excel como de las macros en VBA!
No solo tenemos que tener en cuenta las limitaciones de los números de serie
de fechas en Excel, o el límite del año 100 en VBA, sino que debemos
asegurar que las fechas se corresponden con fechas del Calendario Gregoriano
según el primer día en que se decretó su uso en cada país (en la lista
anterior he incluido los 3 días más importantes en que se pasó a usar el
Calendario Gregoriano y se dejó de usar el Calendario Juliano.
6) Plantilla con fechas Excel y VBA
Descarga la plantilla totalmente gratuita, con las macros visibles y las
hojas protegidas sin contraseña, desde Google (con el botón "Excel
Download") o desde el enlace a Microsoft OneDrive:
Brainteaser and Python sets
-
The ABC Radio web site has a regular Friday brain teaser: If you want to
solve the problem yourself, read no further because a spoiler follows!
Investigati...
Excel Golf Score Tracker by Player and Course
-
I made this Excel Golf Score Tracker for anyone who loves Excel, and golf!
Set it up at the start of golf season, then record your results after every
roun...
Este blog pasa a ser un archivo histórico
-
Durante muchos años he utilizado este blog para compartir artículos,
reflexiones, recursos y experiencias relacionadas con la tecnología, la
administrac...
Números de pastel
-
Estos números constituyen una extensión natural de los traducidos como
“Catering perezoso” (o también como “Cortador perezoso”), que ya se han
estudiado ...
Cómo hacer gráficos en Excel
-
Excel es una de las herramientas más potentes y versátiles para el análisis
y la presentación de datos. Los gráficos en Excel no solo ayudan a
visualizar...
Análisis DAFO (FODA, DOFA) las decisiones con Excel
-
Para conocer la situación de una empresa, proyecto o persona, recurrimos al
análisis DAFO (FODA, DOFA) en la toma de decisiones con Excel. El los años
sese...
How To Predict Bearing Life With Excel
-
When you work in mechanical engineering, understanding the reliability and
performance of bearings under various conditions is crucial. Bearings are
the co...
TikTok’s search evolution
-
2 in 5 Americans use TikTok as a search engine. Nearly 1 in 10 Gen Zers are
more likely to rely on TikTok than Google as a search engine. More than
half of...
Unblocking and Enabling Macros
-
When Windows detects that a file has come from a computer other than the
one you're using, it marks the file as coming from the web, and blocks the
file....
London Excel Meetup Workbooks
-
The workbooks used in my presentation on “Analytical and Interactive
Dashboards in Excel” at the London Excel Meetup, September 3, 2020
International Keyboard Shortcut Day 2019
-
The first Wednesday of every November is International Keyboard Shortcut
Day. This Wednesday, people from all over the world will become far less
efficient...
Welcome, Prashanth!
-
Last March, I shared that we were starting to look for a new CEO for Stack
Overflow. We were looking for that rare combination of someone who… Read
more "W...
Salvador Sostres, analfabeto profesional
-
Los nuevos tiempos traen nuevas profesiones. Internet, además, ha
revolucionado el mundo del periodismo y la palabra escrita. Adaptarse o
morir, ese es el ...
Planificación de compras
-
Realizar una lista con los productos que necesitamos y que formarán parte
de nuestra cesta de la compra nos ayuda a *encontrar la combinación de
bienes p...
Mis metas son seguir superando nuevos retos en Excel y compartirlos en mi blog, para sacarle todo el poder a esta excelente herramienta multiusos, tan usada y a la vez tan incomprendida, para así poder mejorar nuestros conocimientos de Excel.
Calendario_Laboral_Festivos_Dinámicos_PW1.xlsx
