Calendario con fórmula matricial en Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Deseamos insertar un nuestra hoja un calendario con fórmula matricial en Excel (mensual).

El calendario nos permite consultar por mes y año, el día del mes. Aplicamos el método de "John Walkenbach" de fórmulas matriciales.

Emplearemos las siguientes funciones, anidándolas:

  • Función SI. Especifica un texto lógico para ejecutar.

  • Función FECHA. Devuelve una fecha determinada.

  • Función MES. Convierte un número en el mes correspondiente.

  • Función AÑO. Convierte un número en el año correspondiente.

  • Función DIASEM. Convierte un número en el día de la semana correspondiente.

  1. SERIE DE DÍAS. Ir a: Celda B4 / Escribir: Lun.

  2. Ir a: Celda B4 / Situar el cursor del ratón sobre el punto inferior derecho / Clic y mantener pulsado / Arrastrar hasta la celda H4. Insertará la serie de los días de la semana.

    Calendario con fórmula matricial: Rango B4:H4 con serie de días (formato corto).
  3. FORMULA MATRICIAL. Seleccionar el rango: B5:H10. Rango donde insertar los días del calendario.

  4. Ir a: Barra de fórmula / Escribir:

    =SI(MES(FECHA(AÑO(B3);MES(B3);1))<>MES(FECHA(AÑO(B3);MES(B3);1)-(DIASEM(FECHA(AÑO(B3);MES(B3);1))-1)+{-1;0;1;2;3;4;5;6}*7+{1\2\3\4\5\6\7});"";FECHA(AÑO(B3);MES(B3);1)-(DIASEM(FECHA(AÑO(B3);MES(B3);1))-1)+{-1;0;1;2;3;4;5;6}*7+{1\2\3\4\5\6\7})
  5. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús. y mantener las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial.

  6. FORMATO CELDA DÍAS. Seleccionar el rango: B5:H10 / Botón derecho del ratón / Formato de celdas. Mostrará el cuadro de diálogo de Formato de celdas.

  7. Ir a: Pestaña Número / Categoría: Personalizada / Tipo: Escribir: d. Aplicará formato fecha que muestre sólo el número del día de la fecha.

    Calendario con fórmula matricial en Excel: Rango B6:H10 con fórmula matricial de los días de la semana
  8. FORMATO CONDICIONAL. Seleccionar el rango B5:H10. Rango con formato condicional para aplicar relleno a las celdas con los días del mes.

  9. Ir a: Cinta de Opciones / Pestaña: Inicio / Grupo: Estilos / Formato Condicional / Nueva regla. Mostrará el cuadro de diálogo de nueva regla de formato.

  10. Ir a: Seleccionar un tipo de regla / Clic sobre: Utilice una fórmula que determine las celdas para aplicar formato.

  11. Ir a: Dar formato a los valores donde esta fórmula sea verdadera / Escribir: =B5<>"". Fórmula para aplicar formato en las celdas del mes seleccionado.

  12. Pulsar el botón Formato / Pestaña: Relleno / Aplicar el color deseado. Para identificar las celdas del mes.

    Calendario con fórmula matricial en Excel: Rango B5:H10 con formato condicional
  13. MES DE REFERENCIA. Seleccionar el rango B3:H3 / Ir a: Pestaña Inicio / Grupo: Alineación / Combinar y centrar. Combinará las celdas del rango.

  14. Ir a: Celda B3 / Escribir: =FECHA.MES(B2;C2). Función que sumara o restará los meses, para la consulta del calendario (al completar el proceso).

  15. CONTROL BARRA DE DESPLAZAMIENTO. Ir a: Celda B2 / Escribir: 01/01/2000. Como fecha de referencia inicial para el calendario.

    Para insertar el control necesitamos tener activa en la cinta de opciones la pestaña de programador. Ir a: Pestaña Archivo / Opciones. Mostrará el cuadro de diálogo de Opciones de Excel.

    Ir a: Categoría: Personalizar cinta de opciones / Ir a: Pestaña principal / Activar la casilla: Programador. Activara la pestaña en la cinta de opciones.

  16. Ir a: Pestaña Programador / Grupo: Controles / Insertar / Controles de formulario / Barra de desplazamiento / Clic sobre cualquier celda. Se inserta el control en la hoja.

  17. PROPIEDADES DEL CONTROL. Situar el cursor del ratón sobre el control / Botón derecho / Formato de Control. Muestra el cuadro de diálogo.

  18. Ir a: Pestaña Control / Valor actual= Escribir:159 (para el año 2013) / Valor mínimo= Escribir: 0 (para fecha mínima 2000) / Valor máximo= Escribir: 400 (para fecha máxima 2033) / Incremento= Escribir: 1 / Vinular con la celda= Escribir C2 (inserta la referencia del control.

  19. POSICIÓN CONTROL. Situar el cursor sobre el control / botón derecho del ratón / Arrastrar para cubrir el rango B2:H2. Ocultando los datos de referencia en B2:C2.

Calendario con fórmula matricial en Excel: Resultado del calendario mensual
Verificar como al pulsar sobre el control se actualiza el calendario mes a mes aumentando o reduciendo la fecha.

Así podemos insertar un nuestra hoja un calendario con fórmula matricial en Excel (mensual).

Nivel de dificultad: Avanzado Excel Avanzado

12 comentarios en “Calendario con fórmula matricial en Excel

  • Hola es muy interesante esta formula, pero hay algunos errores que me salen a mi en mi sistema tengo que sustituir (;) por una (,), supongo que es por la configuracion de idioma, todos los ejercicios me han salido bien hasta este me marca error en el primera llave en el -1 sabras porque

    • Hola Hector,

      En efecto hay algunas incompatibilidades del idioma inglés español, como dices en las fórmulas hay que reemplazar punto y coma (;) por el punto (.), las dobles comillas (“) por comilla simple (‘), y el nombre de las funciones según la versión de Excel. Y recordar insertar como fórmula matricial como se describe en el artículo.

      Gracias por tu comentario.

      Trucos y Cursos de Excel.

      • Gracias hola ya realice los cambios, ya no me sale error en la formula. ahora no me arroja el resultado deseado. te mando mi formula para ver si la puedes checa e indicarme en que me esta fallando:

        =si(MES(FECHA(AÑO(B3),MES(B3),MES(B3),1))MES(FECHA(AÑO(B3),MES(B3),1)-(DIASEM(FECHA(AÑO(B3),MES(B3),1))+
        {-1;0;1;2;3;4;5;6}*7+{1\2\3\4\5\6\7}),´´,FECHA(AÑO(B3),MES(B3),1)-(DIASEM(FECHA(AÑO(B3),MES(B3),1))-1)+{-1;0;1;2;3;4;5;6}*7+{1\2\3\4\5\6\7})

        en las llaves ya cambie el (;) por (.), pero me marca error al igual que con (,) y (:)

  • Gracias por publicar y compartir estos ejercicios de Excel.

    Tengo un problema al construir le Calendario con Fórmula Matricial

    En el paso 14. indica que en la celda B3 hay que poner =FECHA.MES(B2;C2)
    y en las imágenes muestra como consecuencia que tendría que poner “Mayo, 2013”.

    Pero en Excel 2013 me muestra el número de serie de la fecha que es el número indicado de meses antes do después de la fecha inicial.

    ¿Pueden indicarme que formula usar para conseguir que aparezca el Mes y el Año del calendario mostrado?

    Saludos y gracias anticipadas.

    • Hola José Luis,

      Siempre que tengamos el número de serie de una fecha, se puede convertir en fecha aplicando formato a la celda:
      – Seleccionar la celda B3 / Botón derecho del ratón / Formato de celdas. Mostrará ventana formato.
      – Pestaña: Números / Categoría: Fecha / Seleccionar: marzo – 12. O el formato deseado.

      Saludos.

  • Hola Amigo, será que por favor me podrías enviar por correo el Excel para poder implementarlo, no se por que no me funciona.
    De antemano muchas gracias.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *