Calendario de Excel con una fórmula

En esta publicación veremos cómo crear un calendario de Excel con una sola fórmula. Específicamente, escribiremos una fórmula que muestre los días de cualquier mes en formato de calendario gráfico. Nuestro calendario gráfico muestra los días del mes especificado en 7 columnas (de domingo a sábado) e incluye una fila para cada semana. La función clave en nuestra fórmula es SECUENCIA, y es el motivo principal de la publicación. Las otras funciones utilizadas en la fórmula ayudan a garantizar que los días se alineen en la columna del día correcto. Veamos como hacer un Calendario de Excel con una fórmula

Objetivo – Calendario Excel

Antes de llegar demasiado lejos, echemos un vistazo al resultado deseado. Nos gustaría que el usuario pudiera ingresar cualquier mes y año.

Y nos gustaría que el calendario se muestre en nuestra hoja de trabajo así:

Cuando un usuario ingresa un mes o año diferente, queremos que Excel actualice automáticamente el calendario.

Tutorial en vídeo

Narrativa – Fórmula del calendario

Crearemos nuestro calendario siguiendo los siguientes tres pasos:

• Configuración
• Fórmula
• Productos cosméticos

Hagámoslo.

Nota: no todas las versiones de Excel incluyen la función SECUENCIA. La forma más rápida de determinar si una versión de Excel admite la función SECUENCIA es escribiendo =SEQ en cualquier celda y viendo si SECUENCIA aparece en la lista desplegable. Si su versión de Excel no admite la función SECUENCIA, existen otros métodos para crear un calendario que se analizan aquí y aquí .

Configuración

Celdas de entrada

Primero, necesitamos configurar las celdas de entrada. Tenemos dos formas de hacer esto. Podemos permitir que el usuario ingrese un número de mes y un número de año en diferentes celdas, o hacer que ingrese una fecha en una sola celda. De cualquier manera está bien.

Si desea que ingresen el mes y el año por separado, cree algunas etiquetas como Mes y Año y proporcione un par de celdas de entrada junto a ellas. Si lo desea, aplique el estilo de celda de Entrada (Inicio Estilos de celda Entrada) para identificarlas.

Otra opción es permitir al usuario ingresar una fecha, como esta:

Lo clave a tener en cuenta es que para que la fórmula que se presenta a continuación funcione correctamente, la fecha ingresada debe ser el día 1 del mes, como el 1/1/2030. Si el usuario ingresa un día distinto al primer día del mes, la siguiente fórmula producirá resultados inesperados. Puede solucionar este problema modificando la fórmula, aplicando validación de datos o utilizando otros métodos. Pero, en esta publicación, lo mantendremos simple y permitiremos al usuario ingresar el mes y el año por separado.

Etiquetas

A continuación, necesitaremos configurar los encabezados básicos del calendario para mostrar el mes y el año junto con los días de la semana. Finalmente, después de aplicar todos nuestros cosméticos, nos gustaría que se vieran así:

En este punto del proceso, no nos preocupamos por su estilo y formato, por lo que lucirán así por ahora:

Utilizará una fórmula en B10 para mostrar la fecha. Si optó por darle al usuario una celda de entrada de fecha única (por ejemplo, en la celda C5), usaría una referencia de celda directa como esta:

=C5

Si optó por darle al usuario celdas de entrada de año y mes separadas (por ejemplo, en las celdas C6 y C5), usaría la función FECHA de esta manera:

=FECHA(C6,C5,1)

Usamos 1 para el argumento del día para que devuelva el primer día del mes.

Para las etiquetas de los días, ingresé S, M, T, W, T, F y S manualmente. También puedes introducir fácilmente abreviaturas de tres letras, como Sol u otras etiquetas, según desees.

Una vez completada la configuración, es hora de crear nuestro calendario de Excel con una fórmula.

Fórmula del calendario de Excel

Dado que se trata de Excel, hay muchas formas de escribir esta fórmula de calendario y la solución presentada es solo una opción posible.

SECUENCIA

En el corazón de esta fórmula se encuentra la función SECUENCIA. La función SECUENCIA devuelve una secuencia de números. Comencemos mirando los dos primeros argumentos. Le dicen a la función SECUENCIA cuántas filas y columnas crear. Por ejemplo, la siguiente fórmula creará un rango de 6 filas y 7 columnas:

=SECUENCIA(6,7)

Pulsamos enter y crea esta salida:

En realidad, esto se acerca bastante a lo que finalmente queremos. Pero, ¿cómo nos aseguramos de que los números de los días se muestren en las columnas correctas de los días de la semana? Bueno, si logramos que el primer día del mes aparezca en la columna correcta, los días restantes naturalmente se alinearán. Entonces, hagamos que el primer día del mes aparezca en la columna de días laborables correcta.

DÍA LABORABLE

Para empezar, debemos poder determinar el día de la semana para el primer día del mes. Afortunadamente, Excel tiene la función DÍA SEMANAL que hace precisamente eso. Le proporcionamos una fecha y nos dice su valor de día laborable. Dado que la celda que usamos para mostrar el encabezado de nuestro calendario B10 ya contiene la fecha del primer día del mes, podemos usar esto:

=DÍA DE LA SEMANA(B10)

De forma predeterminada, devuelve 1 cuando el día laborable es domingo; 2 para el lunes; etcétera. Hay un segundo argumento opcional que puede usar si desea cambiar el valor devuelto según el día, pero en nuestro caso, el valor predeterminado es perfecto.

Ahora que sabemos cómo determinar el día de la semana para el primer día del mes, necesitamos usar esta información para actualizar la función SECUENCIA original.

ELEGIR

El tercer argumento de la función SECUENCIA nos permite especificar el número inicial. Por ejemplo, si quisiéramos que la secuencia comenzara en el número 10 en lugar del 1 predeterminado, podríamos usar esto:

=SECUENCIA(6,7,10)

Esto crearía este rango:

En nuestro caso, queremos que la secuencia comience en cualquier número necesario para garantizar que el día 1 aparezca en la columna correcta.

Por ejemplo, si el primer día del mes es domingo, queremos que nuestra secuencia comience en 1 para que el número 1 aparezca en la primera celda (la columna del domingo). Sin embargo, si el primer día del mes cae en lunes, queremos que la secuencia comience en 0 para que 1 termine en la segunda celda (la columna del lunes). Si el primer día del mes cae en martes, queremos que la secuencia comience en -1 para que 1 termine en la tercera celda (la columna del martes). Etcétera. Hay varias formas de abordar esto. La opción que discutiremos utiliza la función ELEGIR.

La siguiente fórmula devolverá los resultados que necesitamos. Es decir, devolverá 1 cuando el día laborable sea domingo; 0 cuando el día laborable es lunes; -1 cuando el día laborable es martes; -2 cuando es miércoles; etcétera:

=ELEGIR(DÍA DE LA SEMANA(B10),1,0,-1,-2,-3,-4,-5)

Ahora, dado que esto calcula el valor inicial de secuencia correcto, podemos usarlo como tercer argumento de la función SECUENCIA, así:

=SECUENCIA(6,7,ELEGIR(DÍA DE LA SEMANA(B10),1,0,-1,-2,-3,-4,-5))

Pulsamos Enter y bam… nuestra fórmula de calendario de Excel tiene buena pinta:

Esto es perfecto porque el día 1 aparece en la columna correcta.

Con nuestros días en las columnas correctas, solo queda un poco de cosmética.

Productos cosméticos

Ocultaremos los números de los días que no están en el mes actual y diseñaremos los encabezados del calendario.

Ocultar números de días

Para ocultar los números de días que no pertenecen al mes actual, aplicaremos formato condicional. Crearemos dos reglas separadas… una para ocultar los números menores que 1 y otra para ocultar los números mayores que el último día del mes.

Seleccionamos todo el rango del calendario y luego Inicio Formato condicional Resaltar reglas de celda Menos que . En el cuadro de diálogo resultante, ingresamos 1 y seleccionamos Formato personalizado …

En el cuadro de diálogo Formato de celdas resultante, seleccionamos Personalizado y luego ingresamos tres puntos y coma:

Bam:

Ahora, ocultemos los números mayores que el último día del mes. Inicio Formato condicional Resaltar reglas de celda Mayor que . Luego ingresamos la fórmula que se muestra en el cuadro de diálogo a continuación, que calcula dinámicamente el número de día del fin del mes (último día del mes):

Usamos el mismo formato personalizado de tres punto y coma y bam:

Con nuestros días apareciendo como deseamos, ahora podemos volver a visitar los encabezados del calendario.

Encabezados

Primero, ajustemos el formato de fecha de la celda B10 del encabezado de nuestro calendario. En lugar de mostrar un formato de fecha corto, como 1/1/2030, debería mostrar el nombre del mes completamente escrito. Hacemos esto seleccionando la celda B10 y abriendo el cuadro de diálogo Formato de celdas. Luego, seleccionamos Personalizado e ingresamos mmmm yyyy así:

Luego podemos seleccionar todas las celdas en la fila del encabezado y abrir el cuadro de diálogo Formato de celdas nuevamente. Haga clic en Alineación Centrar en la selección Aceptar. Finalmente, podemos aplicar cualquier formato adicional que deseemos, como aplicar un estilo de celda, fuente en negrita o un tamaño de fuente más grande.

Si lo desea, podemos aplicar el estilo de celda explicativa a las etiquetas de los días y completar las celdas como desee. También podemos aplicar el estilo de celda explicativa a las columnas de domingo y sábado si lo deseamos. El calendario resultante se ve así:

Sí… ¡lo logramos!

Ahora, el usuario puede cambiar el mes y año deseado para el calendario según lo desee. Febrero de 2030 se vería así:

Marzo de 2030:

Y bueno… ya entiendes la idea

Conclusión

Si tiene alguna sugerencia sobre cómo mejorar esta fórmula del calendario de Excel, o tiene alguna fórmula alternativa, compártala publicando un comentario a continuación… gracias

Archivo de muestra

Calendario gráfico.xlsxDescargar

Notas adicionales

En lugar de usar formato condicional para ocultar los días que quedan fuera del mes actual, podríamos incluir una función de TEXTO alrededor de nuestra fórmula, como esta:

=TEXTO(SECUENCIA(6,7,ELEGIR(DÍA DE LA SEMANA(B10),1,0,-1,-2,-3,-4,-5))," ["DÍA(EOMES(B10,0)) "];;#")

Una versión un poco más compacta de la fórmula usa MOD en lugar de ELEGIR, así:

=TEXTO(SECUENCIA(6,7,-MOD(DÍA SEMANAL(B10,2),7)+1)," ["DÍA(EOMES(B10,0))"];;#")