Cómo hacer un calendario anual en Excel: guía para empezar

Un calendario anual en Excel es una hoja (o un libro entero) que muestra los 12 meses del año para que anotes vacaciones, cumpleaños, plazos o lo que necesites sin perderte entre fechas sueltas. En este tutorial lo construyes pieza a pieza: empiezas con un solo mes, sigues con los 12 en un libro, marcas los fines de semana y los festivos con formato condicional y terminas con una hoja "Resumen" que muestra el año entero de un vistazo. Las funciones que vas a usar —FECHA, DIASEM, FIN.MES, DIAS, BUSCARV y SI.ERROR— están en Excel 2007 en adelante, y la pequeña macro de VBA funciona desde Excel 2010 sin cambios.

Qué es un calendario anual en Excel y para qué te sirve

Un calendario anual en Excel es un libro con una hoja (o varias) donde los 12 meses aparecen ordenados para que sepas en qué día cae cada fecha y puedas escribir al lado lo que toca ese día. A diferencia de un calendario de pared, aquí los días no son números fijos: los calcula Excel a partir de la fecha que tú escribes, y el formato se encarga de presentarlos como "jueves 1", "viernes 2" y así.

Te sirve para planear el año: vacaciones, cumpleaños, reuniones, plazos de facturas, turnos de trabajo, entregas. La ventaja frente a imprimir uno es que el calendario se adapta solo cuando cambias de año (cambias 2026 por 2027 y los días se recolocan), puedes filtrar, ordenar y aplicar formato condicional para ver de un vistazo qué días son festivos o libres.

Para armarlo usas fórmulas de fecha (FECHA, FIN.MES, DIASEM), formato de número para que los números parezcan días de la semana, una matriz 7×6 donde caen los días del mes y formato condicional para pintar sábados, domingos y festivos. Vamos a verlo paso a paso sobre un libro de ejemplo llamado "Mi calendario 2026.xlsx".

Las funciones de fecha que mueven el calendario

Las cuatro funciones que hacen el trabajo pesado son FECHA, DIASEM, FIN.MES y DIAS. Todas viven en Excel 2007 y posteriores, así que las tienes en cualquier versión moderna. Empezamos con la celda A1 = 01/01/2026, que será el punto de partida de todo el calendario.

FECHA: el primer día del año sin teclearlo

FECHA (en inglés DATE) arma una fecha a partir de sus tres piezas: año, mes y día. Es más fiable que escribir la fecha a mano, porque no depende de cómo tengas configurado el ordenador.

=FECHA(2026;1;1)

Esta fórmula en A1 devuelve 01/01/2026, que es jueves. Si la arrastras hacia abajo con el controlador de relleno hasta A12, tienes 12 celdas con los días 1 de enero a 1 de diciembre. Para no escribir cada mes a mano, usa FILA para que el número del mes salga solo:

=FECHA(2026;FILA(A1);1)

Al arrastrar hasta A12, FILA(A1) va devolviendo 1, 2, 3… 12 y obtienes los días 1 de cada mes sin teclear nada más.

DIASEM: el número del día de la semana

DIASEM (en inglés WEEKDAY) te dice qué día de la semana es una fecha. El segundo argumento es importante: con 2, la semana empieza en lunes y devuelve 1 para lunes y 7 para domingo. Es el ajuste que vas a usar siempre en calendarios.

=DIASEM(A1;2)

Sobre A1 = 01/01/2026 (jueves), devuelve 4. Si usas el segundo argumento 1, el domingo vale 1 y el sábado vale 7: es el orden anglosajón y el motivo número uno de calendarios que empiezan en domingo sin que quieras.

FIN.MES y la cantidad de días del mes

FIN.MES (en inglés EOMONTH) devuelve el último día del mes de la fecha que le pases. Con segundo argumento 0 es el último día del mismo mes; con -1, el del mes anterior.

=FIN.MES(A1;0)

Sobre A1 = 01/01/2026 devuelve 31/01/2026: enero tiene 31 días. Para saber cuántos días tiene el mes sin pensarlo, restas el último día de este mes menos el último del mes anterior:

=FIN.MES(A1;0)-FIN.MES(A1;-1)

Sobre enero de 2026 da 31; sobre febrero de 2026 da 28; sobre febrero de 2028 (bisiesto) da 29. Esta cuenta es la que usaremos para que la matriz del mes no muestre días de más.

Un mes en una hoja con los días bien colocados

La pieza central del calendario es una matriz de 7 columnas (una por día de la semana, de lunes a domingo) y 6 filas (las semanas que puede tener un mes). Aunque muchos meses caben en 5 semanas, la sexta fila cubre los meses largos que se desbordan, como enero de 2026.

Empezamos en una hoja llamada "Mes". En B1 va el mes que estamos armando (en forma de fecha: 01/01/2026 con formato "mmmm aaaa" para que se vea "enero 2026"). En B2 calculamos el último día del mes con FIN.MES, que nos sirve de tope. En la fila 4 escribimos los nombres de los días, y desde B5 comienza la matriz.

La matriz de 7 columnas y 6 filas

La fórmula que rellena la matriz es la misma para todas las celdas: copia la fecha del encabezamiento de su columna y, si todavía no ha llegado al último día del mes, suma 1; si no, deja la celda en blanco. La clave es que cada celda mira su vecina de arriba, no tiene valores fijos.

=SI(B4<$B$2;B4+1;"")

En B4 pones la fecha del primer lunes que toca (o el día 1, si prefieres empezar por ahí). Las celdas B5 a H5 miran B4, B4+1, B4+2… Si esa fecha cae dentro del mes (es menor que B2, que es FIN.MES del mes), la muestran; si no, devuelven "". En la fila 5, la fórmula en B5 mira a B4; en C5, a C4; y así hasta H5.

=SI(C4<$B$2;C4+1;"")

El truco que hace que esto funcione al arrastrar es que $B$2 va siempre con dólares (referencia absoluta), y la referencia de arriba (B4, C4, D4…) es relativa, así se mueve al copiar pero el tope no.

Formato de día de la semana y de número de día

Para que la celda no muestre "01/01/2026" sino "jueves 1", aplicas un formato de número personalizado. Selecciona B5:H10, abre Formato de celdas con Ctrl+1 y en la pestaña Número escribe el código de formato:

dddd es el nombre completo del día de la semana y d es el número de día. Sobre 01/01/2026 sale "jueves 1". Si prefieres el número corto (dd) y la inicial del día (ddddd), el formato sería ddddd dd.

Para saber en qué número de semana cae cada fila, escribe en la columna A la fórmula:

=NUM.DE.SEMANA(B5;2)

A la izquierda de cada fila verás "Semana 1", "Semana 2"… según el estándar ISO, donde la semana 1 es la que contiene el primer jueves del año.

Los 12 meses en una sola hoja, uno debajo de otro

Con la hoja "Mes" ya tienes un mes que se rellena solo cuando cambias B1. Para tener los 12 meses en un libro, la opción más limpia es duplicar esa hoja 12 veces, una por mes, y nombrarlas "Enero", "Febrero"… "Diciembre". Cada hoja conserva la matriz y solo cambia el valor de B1.

Duplicar a mano está bien, pero con VBA es un clic. En la primera hoja, a la que llamaremos "Índice", escribiremos los nombres de los 12 meses y los hipervínculos para saltar de una pestaña a otra.

Crear las 12 hojas de un golpe con VBA

Abre el editor con Alt+F11, inserta un módulo (Insertar > Módulo) y pega este código:

Sub CrearMeses()
    Dim nombres(1 To 12) As String
    Dim i As Integer
    nombres(1) = "Enero"
    nombres(2) = "Febrero"
    nombres(3) = "Marzo"
    nombres(4) = "Abril"
    nombres(5) = "Mayo"
    nombres(6) = "Junio"
    nombres(7) = "Julio"
    nombres(8) = "Agosto"
    nombres(9) = "Septiembre"
    nombres(10) = "Octubre"
    nombres(11) = "Noviembre"
    nombres(12) = "Diciembre"
    For i = 1 To 12
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = nombres(i)
        ActiveSheet.Range("B1").Value = DateSerial(2026, i, 1)
    Next i
End Sub

Coloca el cursor dentro de la subrutina y pulsa F5. Verás cómo aparecen 12 pestañas nuevas al final del libro. Cada hoja tiene en B1 el día 1 de su mes. Ahora copia a cada hoja la matriz B5:H10 de "Mes" y listo.

Enero   Febrero   Marzo   Abril   Mayo   Junio   Julio   Agosto   Septiembre   Octubre   Noviembre   Diciembre   Hoja1   Índice

Un índice con enlaces en la primera hoja

En la hoja "Índice", escribe en A1 el primer nombre de mes ("Enero") y en B1 la fórmula que crea el hipervínculo:

=HIPERVINCULO("#'"&A1&"'!A1";"Ir a "&A1)

Arrastra hacia abajo hasta A12 (con sus correspondientes B1, B2…). La función HIPERVINCULO (en inglés HYPERLINK) arma la dirección "#'NombreHoja'!A1" concatenando el nombre de la hoja y la celda destino. Al hacer clic en cada enlace, saltas a esa pestaña.

Pintar los fines de semana y los festivos sin hacerlo a mano

De nada sirve un calendario si no distingue de un vistazo los días laborables de los fines de semana y los festivos. Con formato condicional, las celdas se pintan solas según la fecha que contienen.

Sábado y domingo con formato condicional

Selecciona el rango B5:H10 de la hoja "Mes". Abre Inicio > Estilos > Formato condicional > Nueva regla y elige "Utilice una fórmula que determine las celdas a las que se aplica el formato".

La fórmula =DIASEM(B5;2)>5 devuelve VERDADERO cuando el día es sábado (6) o domingo (7). El segundo argumento 2 es el que pone lunes=1, así que 6 y 7 caen en fin de semana. Las celdas que cumplen la condición se rellenan de gris claro.

Los festivos del año con una lista y una regla

Crea una hoja nueva llamada "Festivos". En A2:A20 escribe las fechas festivas del año (en tu formato, día/mes/año). La regla de formato condicional usa CONTAR.SI (en inglés COUNTIF) para ver si la fecha de la celda está en esa lista.

=CONTAR.SI(Festivos!$A:$A;B5)=1

Sobre la hoja "Mes", con B5:H10 seleccionado, vuelve a Inicio > Estilos > Formato condicional > Nueva regla > Utilice una fórmula. Pega la fórmula anterior y aplica un relleno rojo suave. Ahora, cada celda cuya fecha esté en la hoja "Festivos" se pinta de rojo.

El calendario anual en una sola página con BUSCARV

Con las 12 hojas tienes el año entero, pero tienes que cambiar de pestaña para verlo todo. La hoja "Resumen" resuelve eso: 12 columnas (una por mes) y 31 filas (una por día posible), de modo que el año cabe en una sola pantalla.

La clave mes-día que enlaza con cada hoja

En la hoja "Resumen", la columna A tiene los días 1 a 31 y la fila 1 tiene los nombres de los meses. Para que BUSCARV (en inglés VLOOKUP) sepa qué buscar, en cada hoja de mes creas una columna auxiliar con la clave "mes-día".

En la hoja "Enero", por ejemplo, en una columna a la derecha (por ejemplo la J) escribes:

=TEXTO(MES(B4);"0")&"-"&TEXTO(DIA(B4);"0")

Sobre el 01/01/2026 esto devuelve "1-1". Repite la fórmula en cada celda de la matriz. Ahora en la hoja "Resumen", en A2:A32 escribes 1, 2, 3… 31 y en B1:M1 los nombres "Enero", "Febrero"… "Diciembre". En B2 la fórmula que busca la fecha es:

=SI.ERROR(BUSCARV($A2&"-"&B$1;'Enero'!$J:$J;1;FALSO);"")

SI.ERROR para que los días que no existen queden en blanco

Sin SI.ERROR, el 31 de febrero mostraría #N/A porque BUSCARV no encuentra "2-31" en la hoja "Febrero". Con SI.ERROR (en inglés IFERROR), el segundo argumento se queda vacío y la celda se ve limpia.

=SI.ERROR(BUSCARV($A2&"-"&B$1;HojasMes;1;FALSO);"")

El truco es que cada celda de "Resumen" apunte a la hoja de su mes: B2 mira 'Enero', C2 mira 'Febrero', y así hasta M2 con 'Diciembre'. Para no escribir 12 BUSCARV distintas, define un nombre de rango por hoja o, si tienes BUSCARX (en inglés XLOOKUP) en Excel 2021 o 365, simplifica la fórmula.

Por qué tu calendario falla y cómo arreglarlo

Cuatro tropiezos típicos al armar el calendario. Cada uno tiene una causa clara y un arreglo que cabe en una línea.

La celda muestra 00/01/1900 en vez de estar vacía

La fórmula devuelve 0 cuando no hay día que mostrar, y Excel interpreta 0 como 00/01/1900. El arreglo es envolver la fórmula en un SI que devuelva texto vacío, o aplicar el formato personalizado d;; (formato de número con tres partes: positivos; negativos; cero), que oculta el cero por completo.

El día de la semana va de domingo a sábado

Si DIASEM devuelve 1 para domingo, escribiste la fórmula con el segundo argumento 1 o lo omitiste (en ese caso, Excel toma 1 por defecto). Cámbialo a 2: lunes vale 1 y domingo vale 7.

El formato condicional no pinta las celdas con fórmula

La regla se creó antes de pegar la fórmula, así que se aplicó al rango que estaba vacío. Vuelve a crear la regla con B5:H10 ya relleno, o revisa que "Se aplica a" en el Administrador de reglas de formato condicional incluya el rango correcto.

BUSCARV devuelve #N/A en todo el resumen

La clave "mes-día" se construye al revés, o una de las dos partes está en texto y la otra en número. Usa TEXTO (en inglés TEXT) en ambos lados para asegurar el mismo formato: =TEXTO($A2;"0")&"-"&TEXTO(B$1;"0").

Lo que ya sabes hacer con el calendario anual

• Armar un mes con FECHA y FIN.MES, y manejar la matriz 7×6 con un SI que oculta los días que no tocan.
• Apilar 12 hojas, una por mes, con una macro corta que las crea y las nombra.
• Pintar sábados, domingos y festivos con dos reglas de formato condicional que miran DIASEM y una lista.
• Resumir el año entero en una hoja con BUSCARV y SI.ERROR, uniendo mes y día en una clave de texto.
• Diagnosticar los cuatro errores más comunes y dejarlos en blanco o con el formato correcto.

El siguiente paso natural es añadir una columna de notas por día. En la hoja "Resumen", al lado de cada fecha, monta un menú desplegable con Validación de datos (Datos > Herramientas de datos > Validación de datos) y una lista como "Vacaciones", "Cumpleaños", "Reunión", "Plazo". Cada celda aceptará solo esos valores, y tu calendario pasará de ser un esqueleto a ser tu agenda del año.

Más información oficial en la documentación de la función FECHA, la ayuda de DIASEM, la página de FIN.MES, la documentación de BUSCARV y la referencia de formato condicional en Excel.

Dudas al armar un calendario anual en Excel