Traducir el blog

Calendario en Excel con una tabla dinámica

🔝To translate this blog post to your language, select it in the top left Google box.


Cómo crear un calendario con una tabla dinámica

En este blog he publicado bastantes calendarios que se pueden ver en el siguiente enlace:

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:

=SECUENCIA("01-01-2101"-"01-01-1901";1;"01-01-1901")

ATENCIÓN: Esta función sólo sirve en las últimas versiones de Excel 365; Web; iPad; iPhone y Android.

Por compatibilidad con versiones desde Excel 2010, es mejor introducir el primer día en la celda A2 y los sucesivos calcularlos con la fórmula: =A2+1

Se trata de rellenar todas las fechas de los siglos XX y XXI.

Son un total de 73.049 días obtenidos con la fórmula: ="01-01-2101"-"01-01-1901"

ATENCIÓN: En una hoja la máxima fecha sería el 24-11-4770: =FILA(A1048576)-1

Esta tabla de fechas será el origen de datos de la tabla dinámica que se creará en la hoja 'Calendario'.

Descarga esta plantilla vacía con los pasos a seguir desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive.

Activa las macros y haz clic en la caja de texto.


  Calendario Dinámico PW0.xlsm 


Pasos a seguir ya explicados en el vídeo

  1. Comienza en una nueva hoja, cambiando su nombre a: Fechas
  2. Ponla en color amarillo.
  3. Edita la tabla de fechas:
  4. En A1 escribe la cabecera: Fecha; Día Semana; Núm. Semana
  5. En A2 pon el día 01-01-1901
  6. En A3 escribe la fórmula: =A2+1
  7. En el menú Vista: Desmarca la Línea de cuadrícula
  8. Inmoviliza la fila superior
  9. Inserta la tabla con cabecera: Edita su nombre: TablaFechas
  10. Cambia su tamaño a: 73050 filas
  11. Cambia su formato a: Fecha corta
  12. ATENCIÓN: Comprueba que se ha rellenado hasta el último día: 31-12-2100
  13. Calcula el día de la semana en B2: =DIASEM([@Fecha];2)
  14. NOTA: El segundo argumento indica con un 2 que la semana comienza en lunes (1) y acaba en domingo (7).
  15. Calcula el número de semana en C2: =NUM.DE.SEMANA([@Fecha];2)
  16. NOTA: El segundo argumento indica con un 2 que la semana comienza en lunes.
  17. Copia las celdas B2 y C2 hacia abajo con autorrelleno.
  18. Centra y ajusta las columnas.
  19. Copia las fórmulas dinámicas de la tabla como valores estáticos.
  20. NOTA: Este paso es recomendable si no va a crecer esta tabla, ya que mejora su rendimiento al no tener que recalcularla.
  21. Inserta la tabla dinámica en una nueva hoja a partir de esta tabla.
  22. Cambia el nombre de la hoja a: Calendario
  23. Ponla en color azul.
  24. Selecciona todas las celdas (arriba a la izquierda de las filas y columnas)
  25. Selecciona menú de Inicio: Alineación
  26. Centrar y Alinear en el medio
  27. Selecciona "Fecha" como: Etiqueta de fila
  28. Agrupa las fechas por: Meses y Años.
  29. Selecciona "Núm. Semana" como: Etiqueta de fila
  30. Selecciona "Día Semana" como: Etiqueta de columna
  31. Selecciona "Fecha" como: Resumen de Valores
  32. En Valores: Cuenta de Fecha Cambia la Configuración de campo de valor…
  33. Resume el campo de valor por: Máx.
  34. ATENCIÓN: Este es el truco principal de este calendario.
  35. Valores como: Máx. de Fecha
  36. ¡¡¡ Y se ven los números de serie de las fechas en Excel !!!
  37. En el menú Vista: Desmarca la Línea de cuadrícula
  38. Selecciona la fila 4: Inmoviliza paneles
  39. En Herramientas de tabla dinámica: Diseño
  40. Totales generales: Desactivar para filas y columnas
  41. ¡Ya tenemos el esqueleto del calendario montado con una tabla dinámica!
  42. Cambia los nombres de las etiquetas:
  43. B2: Máx. de Fecha por: Calendario
  44. B3: Etiquetas de fila por: Año / Mes / Semana
  45. C2: Etiquetas de columna por: Día
  46. C3 a I3: Cambia 1 por lun; … ; 7 por dom
  47. Selecciona la celda B4 (1901):
  48. Haz clic con el botón derecho del ratón y aparece el menú contextual.
  49. Desmarca: Subtotal "Años"
  50. Selecciona la celda B5 (ene):
  51. Haz clic con el botón derecho del ratón y aparece el menú contextual.
  52. Desmarca: Subtotal "Fecha"
  53. Selecciona la celda D6 con el primer día del calendario:
  54. Haz clic con el botón derecho del ratón y aparece el menú contextual.
  55. Selecciona: Configuración de campo…
  56. Haz clic en el botón de abajo a la izquierda: Formato de número
  57. Selecciona Categoría: Personalizada
  58. Cambia el Tipo: Estándar por la letra: d
  59. ¡Perfecto! ¡Has conseguido que los valores de fechas se muestren como los días del mes!
  60. Vamos a conseguir que el calendario sea dinámico.
  61. En Herramientas de tabla dinámica: Opciones
  62. Inserta Segmentación de datos
  63. Marca: Años y Fecha
  64. Selecciona la segmentación de datos: Fecha
  65. En Herramientas de Segmentación de datos: Opciones
  66. Cambia el Título de Fecha por: Meses
  67. Cambia el número de columnas a 12
  68. Sitúa esta segmentación arriba
  69. Amplia su anchura hasta que se vean los nombres de los meses.
  70. En Herramientas de tabla dinámica: Opciones
  71. Desmarca: Botones +/-
  72. Selecciona una celda de la tabla dinámica:
  73. Haz clic con el botón derecho del ratón y aparece el menú contextual.
  74. Selecciona: Opciones de tabla dinámica
  75. Desmarca: Autoajustar anchos de columna al actualizar
  76. Selecciona el rango de columnas C:I
  77. Haz clic con el botón derecho del ratón para mostrar el menú contextual
  78. Selecciona: Ancho de columna…
  79. Pon un valor de: 8
  80. En Herramientas de tabla dinámica: Diseño
  81. Selecciona un estilo a tu gusto…
  82. BONUS: Aplicar formato condicional a los días de entresemana:
  83. Selecciona el rango de celdas: =$C$4:$I$15094
  84. En el menú Inicio: Formato condicional
  85. Haz clic en: Nueva regla
  86. Utiliza la fórmula: =DIASEM(C4;2) 6
  87. Formato: Color azul claro
  88. Haz clic en el botón: Aceptar
  89. BONUS: Aplicar formato condicional a los sábados y domingos (findes):
  90. Selecciona el rango de celdas: =$C$4:$I$15094
  91. En el menú Inicio: Formato condicional
  92. Haz clic en: Nueva regla
  93. Utiliza la fórmula: =Y(DIASEM(C4;2)>5;C4<>"")
  94. Formato: Color rojo claro
  95. Haz clic en el botón: Aceptar"
  96. EXTRAS: Aplicar formato condicional al día de hoy:
  97. Selecciona el rango de celdas: =$C$4:$I$15094
  98. En el menú Inicio: Formato condicional
  99. Haz clic en: Nueva regla
  100. Utiliza la fórmula: =C4=HOY()
  101. Formato: Color amarillo
  102. Haz clic en el botón: Aceptar
  103. Selecciona la celda B5 (ene):
  104. Haz clic con el botón derecho del ratón y aparece el menú contextual.
  105. Selecciona: Configuración de campo...
  106. Selecciona la pestaña: Diseño e impresión
  107. Marca: Insertar línea en blanco después de cada etiqueta de elemento
  108. Con las segmentaciones de datos:
  109. Selecciona el año: 2020
  110. Selecciona 3 meses: de oct a dic
  111. ATENCIÓN: Cómo reducir el tamaño del libro:
  112. Selecciona la fila 27.
  113. Mantén pulsada la tecla: Mayúsculas
  114. Presiona una vez la tecla: Fin
  115. Presiona una vez la tecla de cursor: Flecha hacia abajo
  116. Haz clic en cualquier fila con el botón derecho del ratón
  117. Haz clic en: Eliminar
  118. IMPORTANTE: Guarda el libro inmediatamente.
  119. 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…
  120. ¡¡¡ 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:

  Calendario Dinámico PW1.xlsx 


Como aplicación de las tablas dinámicas he diseñado 3 calendarios más que te pueden interesar:

Comenta si ha sido una pérdida de tiempo o si has aprendido alguna técnica nueva que te haya servido para mejorar en tu dominio de Excel.

noviembre 21, 2020
Keep Reading...

Calendario Perpetuo desde 1900 con eventos

0) Introducción al Calendario Perpetuo

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:

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

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:

  1. Calendario Perpetuo Gregoriano con un rango de fechas del 01-03-1900 al 31-12-9999.
  2. Vista de 3 meses: el mes seleccionado; el anterior y el posterior.
  3. El primer día de la semana puede ser domingo o lunes.
  4. Muestra u oculta los días de otros meses.
  5. Sábados y domingos marcados en color rojo.
  6. Flechas para incrementar o decrementar el mes, rotando como un carrusel.
  7. Flechas para cambiar el año.
  8. Selección de un mes.
  9. Selección de una año mediante 2 cifras para las centenas y 2 cifras para las unidades.
  10. Si se decrementa el año 1900 pasa al año 9999.
  11. Si se incrementa el año 9999 pasa al año 1900.
  12. Selección del día de hoy.
  13. Selección del primer día del calendario perpetuo: 01-03-1900.
  14. Selección del último día del calendario perpetuo: 31-12-9999.
  15. Muestra u oculta un calendario auxiliar y otros datos.
  16. Tabla de fechas con las efemérides guardadas, por ejemplo los Días de Independencia de los países obtenidos de Wikipedia (enlace aquí).
  17. Hasta 12 efemérides por día en 3 páginas con 4 eventos cada una.
  18. BONUS: Al pasar el cursor del ratón sobre una celda muestra los eventos de ese día.
  19. Activa o desactiva el evento del ratón sobre una celda.
  20. Botones de animación con avance y retroceso de los meses y con 5 velocidades de animación.
  21. Botones para ver los primeros o los últimos 3 meses del año.
  22. Botones para incrementar o decrementar de 3 en 3 meses.
  23. 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:




3) Cómo he diseñado este Calendario Perpetuo

Este calendario perpetuo se compone de 3 hojas:

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

B25: =C5+1-DIASEM(C5;Rango_DíaSemana)

K25: =Rango_MesNom+1-DIASEM(Rango_MesNom;Rango_DíaSemana)

T25: =U5+1-DIASEM(U5;Rango_DíaSemana)

Siendo:

Rango_MesNom: ='Año>=1900'!$L$5

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:

=HIPERVINCULO(MouseOver(K26);SI.ERROR(SI(O($Z$3="ü";MES(K26)=$K$5);DIA(K26);"");""))

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:

pedrowave.blogspot.com - Como pintar con Excel


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

{=SI.ERROR(K.ESIMO.MENOR(SI($B$16=Rango_Fechas;FILA(Rango_Eventos)-MIN(FILA(Rango_Fechas))+1;"");FILA()-FILA(B34));0)}

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:

https://pedrowave.blogspot.com/search/label/calendario

noviembre 11, 2020
Keep Reading...

Cómo calcular la edad y el día de la semana antes de 1900

Esta entrada del blog es la segunda de la trilogía sobre el cálculo de fechas anteriores al año 1900 con funciones en VBA. La trilogía explica:

A) Cómo calcular fechas antes de 1900 en Excel y VBA

B) Cómo calcular la edad y el día de la semana antes de 1900

C) Calendario Perpetuo desde antes de 1900


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:

How to Work with Dates Before 1900 in Excel

How to calculate ages before 1/1/1900 in Excel


Pero lo mejor es seguir leyendo...


2) Día de la semana en Excel y en VBA

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:

Calcular el día de la semana en fechas anteriores a 1900

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:

Calcular la diferencia entre dos fechas

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:

Cómo restar dos fechas si alguna es anterior al año 1900


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

Fechas anteriores a 1900 PW1.xlsm 


La voz del usuario de Excel

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?

Microsoft Excel UserVoice: Dates prior to 1900

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:

Dealing with dates before 1900 · Community (microsoft.com)

Atento a la próxima entrada donde explicaré cómo hacer un calendario para meses anteriores al año 1900.

octubre 27, 2020
Keep Reading...

Cómo calcular fechas antes de 1900 en Excel y VBA

🔝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á:

A) Cómo calcular fechas antes de 1900 en Excel y VBA

B) Cómo calcular la edad y el día de la semana antes de 1900

C) Calendario Perpetuo desde antes de 1900

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:


2) Fechas correctas y erróneas en VBA

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:


4) Fechas según norma ISO 8601

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:

Fechas anteriores a 1900 PW1.xlsm 

Con ella ya puedes experimentar el placer de dominar las fechas en Excel y en VBA. Puedes escribir un comentario si tienes alguna duda.

Atento a la próxima entrada donde explicaré cómo calcular la edad y el día de la semana si alguna fecha es anterior al año 1900.

octubre 22, 2020
Keep Reading...

Visor de vídeos en Excel

Desde mi auto-jubilación tengo más tiempo para ver vídeo-tutoriales sobre Excel y, por qué no, también vídeos musicales, series, documentales y películas.

En YouTube hay muchos vídeos para ver, como mi propio Canal YouTube Pedro Wave, pero no me gusta no poder controlar mis listas de reproducción y que aparezcan anuncios cuando lo que quiero es ver un vídeo concreto.

Con Excel puedo tener mi propia lista de reproducción de vídeos, filtrarla y ver los vídeos mediante un control WebBrowser, lo que ya hice en una ocasión anterior:

Blog Pedro Wave for Excel Guys - Reproductor de listas de vídeos

Este proyecto va dedicado a todos los Excelentes YouTubers que publican continuamente videos tutoriales sobre Microsoft Excel y sus herramientas Power Query, Power Pivot, Power View, Power BI, con los que he aprendido tanto.

Con este visor incrustado en Excel puedo tener un repositorio de todos los vídeos que me interesa volver a ver de gurús de Excel como: George Lungu, Chandoo, Jordan Goldmeier, Debra Dalgleish, Sergio Alejandro Campos, Carolina de Andrade, Mynda Treacy, Leila Gharani y tantos otros a los que estoy agradecido.



Pero no sólo puedo ver vídeos sobre Excel sino que también puedo crear listas de vídeos musicales y abrir páginas Web.

A partir de ahora puedo reproducir vídeos en un único visor hecho en Excel (pero sin que se note mucho, por eso oculto todo menos el marco del reproductor), con las siguientes características:

  1. Hoja 'Videos' con el Visor generado con un control WebBrowser que permite incrustar páginas Web.
  2. Hoja 'Video_List' separada con una tabla con la lista de vídeos y páginas Web a reproducir. De momento su edición es manual.
  3. Lista de los primeros 50 vídeos filtrados que son los que se pueden reproducir como máximo en el visor.
  4. Posibilidad de ejecutar el vídeo en 3 modos: Pausa, Play y Bucle.
  5. Edición automática de la colección de PlayList en YouTube.
  6. Presentación en dos modos: como Excel o como Ventana independiente sin ninguna barra ni referencia a Excel.
  7. Control para mostrar u ocultar la barra de título de la ventana de Windows.
  8. En modo Ventana se puede cambiar el zoom del visor del 100% al 25% y así ocupar una pequeña porción de la pantalla.
  9. Hoja 'TD_Videos' auxiliar para filtrar la lista con un filtro avanzado por: Título, Web, Puntos y Clase.
  10. Controles para ir al primer vídeo, al anterior, al siguiente y al último vídeo.
  11. Control para enmudecer el altavoz (mute) u oirlo en YouTube.
  12. Hoja 'HTML' auxiliar para editar automáticamente la página HTML a mostrar, que lleva incrustado el vídeo en código con varios TAG.
  13. Tabla con la ayuda de los controles del visor.
  14. Tabla con las teclas de acceso rápido para controlar el reproductor de YouTube.

Para modificar la lista de vídeos y páginas Web, en la hoja 'Video_List' se debe editar manualmente (lo dejo pendiente de una nueva versión para automatizar la introducción de nuevos vídeos en YouTube) la tabla con las listas de reproducción. Deben incluir uno solo de los dos campos: Id o Web. Para indicar que es un vídeo de YouTube sólo se debe introducir su Id. En cualquier otro caso se deja vacío el Id. y se introduce la página Web completa. Son obligatorios los demás campos: Título, Puntos y Clase.


Descarga de la plantilla

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: Es una versión Beta probada con Excel 2010 en Windows 7 y con una resolución de pantalla de 1920 x 1080 pixeles, por lo que agradeceré tu ayuda como tester de este nuevo visor hecho en Excel.

Si usas Excel 2013 o superior, por defecto está deshabilitado el control WebBrowser. La siguiente página explica cómo habilitarlo a costa de la seguridad, por lo que este cambio debes hacerlo bajo tu responsabilidad, modificando el registro de Window.

Cannot insert certain scriptable ActiveX controls into Office 2013 documents

En el siguiente vídeo puedes ver una demostración del visor de vídeos:

septiembre 10, 2020
Keep Reading...

Mi Carrera Profesional

🔝To translate this blog post to your language, select it in the top left Google box.


Al poco de aprender a hablar aprendemos:

Mi carrera como ingeniero

Todas estas son listas ordenadas como la que he preparado en mi primera semana de ingeniero autojubilado, para simular una escala de tiempos autodinámica y para recordar en el futuro mi carrera profesional en diversas ramas de la ingeniería:
  1. Ingeniero eléctrico durante mis estudios en la Escuela Técnica Superior de Ingenieros Industriales - ETSIIZ de la Universidad de Zaragoza, siendo licenciado de la 2ª promoción de ingenieros.
  2. Ingeniero electrónico diseñando un prototipo de terminal inteligente, entregado como ejercicio práctico de mi proyecto fin de carrera, y que fue dirigido por el gran profesor de electrónica D. Tomás Pollán Santamaría, q.e.p.d. Nunca te olvidaré MAESTRO.
  3. Ingeniero industrial en varias compañías informáticas y departamentos de I+D.
  4. Ingeniero de automatización programando automátas programables.
  5. Ingeniero de software para diseñar equipos electrónicos basados en microcontrolador.
  6. Ingeniero de telecomunicaciones en el departamento de I+D de Electrónica Aragonesa en colaboración con Telefónica I+D.
  7. Ingeniero de sistemas en los departamentos de I+D de Amper y Siemens.
  8. Ingeniero de calidad en un departamento de I+D.
  9. Ingeniero informático desarrollando aplicaciones cliente-servidor como analista-programador.
  10. Ingeniero TIC como consultor de Tecnologías de la Información y las Comunicaciones.
  11. Ingeniero de pruebas de la calidad del software.
  12. Ingeniero de datos para Indra, Alten, Gas Natural Fenosa, IBM y BBVA.
  13. Ingeniero de inteligencia de negocio (BI - Business Intelligence) con herramientas Power.
  14. Ingeniero multimedia desarrollando en Excel múltiples Interfaces Gráficos de Usuario - IGU (GUI - Graphical User Interface) en mi propio blog. Sí, este que estás leyendo ahora mismo: #ExcelPedroWave
La primera es la única formación reglada en ingeniería que he recibido en mi vida profesional, y de eso hace ya más de 40 años y, curiosamente, es la única ingeniería en la que no tengo experiencia. En las demás ingenierías mi formación ha sido vocacional, ocupacional, continua, permanente y autodidacta, y siempre enfocado en obtener los mejores resultados para mi empresa y para mis clientes, y en cumplir los objetivos siguiendo los criterios SMART: específicos; medibles; alcanzables; relevantes y limitados en el tiempo.

Mi carrera profesional

Con esta publicación trato de recordar mi propia carrera profesional en un gráfico en Excel, ¡cómo no!, que muestre los años, las fechas de inicio y fin y los días de cada hito de mi carrera, durante los últimos 45 años, desglosados y filtrados por campos como: Actividad; Cliente; Empresa; Herramientas; Logros; Lugar; Puesto; Sector y Tareas, como se puede ver en esta imagen animada:



Para crearlo han hecho falta 4 hojas:
  • Hoja 'DAT_CARRERA': Con los datos de la carrera en una tabla que se puede editar para introducir tus propios datos profesionales. Antes hay que desproteger la hoja pues está protegida sin contraseña. Observa que la fecha final de un hito está marcada en color de fondo amarillo, con la función =Hoy() pues es el hito actual que aún no ha acabado, seguir publicando en este blog...

  • Hoja 'TD_CARRERA': Con dos tablas dinámicas, una para los campos de cada hito y otra con los datos de la carrera. Las segmentaciones de datos de la hoja 'INF_CARRERA' están conectadas con estas tablas dinámicas.

  • Hoja 'TAB_CARRERA': Con la tabla filtrada asociada al gráfico de barras de la hoja 'INF_CARRERA', y con datos de formato de año y fechas, y con el año mínimo y máximo a mostrar en el gráfico.

  • Hoja 'INF_CARRERA': Con el gráfico de la carrera y las segmentaciones de datos que pueden ser filtradas individualmente o borrados totalmente sus filtros. Cuando por ejemplo se selecciona en la segmentación principal el campo "Empresa", aparece la segmentación de datos secundaria de la "Empresa", para permitir filtrar por empresa. Todos los filtros que se hagan en una segmentación secundaria se mantienen mientras no se borre el filtro de ese campo determinado. Borrando el filtro de la segmentación principal, se borran todos los filtros de las segmentaciones secundarias y la segmentación de los años de inicio.

Descarga de la plantilla

Descarga la plantilla totalmente gratuita, con las macros visibles y las hojas protegidas sin contraseña, desde aquí:

Técnica empleada para la escala de tiempos

Se ha pretendido simular una escala de tiempos autodinámica, para lo que hacen falta unas cuantas macros VBA:
  • Evento Worksheet_SelectionChange en la hoja 'INF_CARRERA' que llama a las macros: ShowHideInitialDate y ShowHideDays, para mostrar u ocultar las fechas y días de cada hito.

  • Evento Worksheet_Change en la hoja 'TD_CARRERA' que llama a las macros: FormatCareerChart y ChangeField, para formater el gráfico y cambiar el campo seleccionado.

  • Evento Worksheet_Change en la hoja 'DAT_CARRERA' que llama a la macro: RefrehPivotTable para refrescar la caché de la tabla dinámica de la hoja 'TD_CARRERA' con los datos de la carrera.

  • Módulo ModCareer con las macros que autodinamizan el gráfico con la carrera y que están suficientemente autoexplicados en los comentarios del código. La función DATE_FORMAT() permite obtener el formato local de las fechas y los años del gráfico.


Agradecimientos

No puedo dejar de agradecer el apoyo y la ayuda que me han ofrecido desinteresadamente todos mis exprofesores y excatedráticos durante 5 años de estudios de ingeniería y todos mis excolegas, excompañeros, excolaboradores, exjefes, exdirectores durante los últimos 40 años de carrera profesional, para conseguir el objetivo de ser un ingeniero autojubilado, con muchas automatizaciones por hacer, muchas rutas que recorrer en autocaravana, como nómada digital, y muchos artículos que escribir en este blog de autoayuda y autoaprendizaje de Excel.
julio 27, 2020
Keep Reading...

Redes Sociales

julio 17, 2020
Keep Reading...

Mis vídeos

Puedes seguirme suscribiéndote a mi canal de YouTube, donde puedes ver gratuitamente la lista completa de mis vídeos desde este enlace:

YouTube - Excel Pedro Wave



Cuando el presupuesto para hacer un vídeo es inmenso, sin ninguna duda se pueden crear vídeos de mucha más calidad que los míos, hechos de forma casera en una tableta con Windows 10 y con sólo tres herramientas:

  1. Excel para Microsoft 365: para crear las plantillas de ejemplo.
  2. MS PowerPoint: para crear el contenido del vídeo.
  3. Editor de vídeo de MS Fotos: para montar y grabar el vídeo.

Y si no, que se lo pregunten a Mark Zuckerberg, que en este vídeo presenta su Metaverso en Facebook, hecho con los últimos adelantos tecnológicos:


julio 17, 2020
Keep Reading...
Suscribirse a: Comentarios (Atom)

Mi lista de blogs

Mostrar todo