Cómo realizar un calendario perpetuo con excel

Cómo realizar un calendario perpetuo con excel

Siempre es útil tener disponible un calendario. Así que vamos a realiza uno con Excel, pero lo haremos de forma que nos pueda servir para todos los años. Simplemente seleccionando el año que queremos mostrar, el calendario se actualizará de forma automática.

Primer paso: El selector de años

La forma sencilla sería dejar una campo de texto donde introducir el año, pero como eso puede provocar errores lo haremos con un selector de valores en forma de lista.
Para conseguirlo creamos una nueva hoja que llamaremos Años. En ella crear la lista de valores con todos los años que vayas a utilizar. En la celda A1 introduce el valor 1900, y en la A2 1901. Ahora seleccionalas, verás que quedan enmarcadas por un marco negro con un punto cuadrado en la esquina inferior derecha. Si arrastras ese punto hacia abajo y excel completará el listado de años de forma automática. El ejemplo llega hasta 9999, pero puedes hacer la lista lo grande que quieras.
lista-añosAhora vuelve a la hoja Calendario y selecciona la celda B3. En la Cinta de opciones, selecciona la solapa Datos y luego pulsa sobre Validación de datos. En la pestaña Configuración del cuadro de diálogo introduce estos valores estos valores:
Permitir: Lista, para que sólo muestre los valores que indiquemos en una lista.
Omitir blancos no muestra lineas vacías en el listado.
Celda con lista desplegable muestra la lista como un selector desplegable.
Origen es el conjunto de celdas que contienen los datos de la lista. Pulsa sobre el botón al final del campo y selecciona la lista de la hoja Años.
Ahora ya no necesitamos ver la hoja Años, asi que pulsa sobre la pestaña con el botón derecho del ratón y selecciona Ocultar.

Segundo paso: El primer mes

Ahora toca el trabajo con fórmulas, el punto fuerte de Excel. Vamos a construir el primer mes del calendario únicamente con el año.

Empezamos por el nombre del mes. En la celda B5 introduce la siguiente fórmula: =FECHA($B$3;1;1) ¿Qué hace esto? Muy sencillo, a partir del valor de la celda B3 (el año) nos da el valor del mes 1 y del día 1. Como lo que queremos es el nombre del mes, cambiaremos el formato de la celda. Pulsa sobre la celda con el botón derecho del ratón y selecciona Formato de celdas… En la pestaña Número selecciona la categoría Personalizada y en Tipo escribe “mmmm”. Ya está, tenemos el texto “enero” como titulo del mes.

formato-mes
Para los nombres de los días puedes optar por escribirlos como texto o utilizar el método anterior. En este caso el formato de la celda será “ddd” para mostrar las tres primeras letras del nombre del día.

La primera semana del mes es la más complicada, ya que dependiendo del primer día del mes estará vacía o no. La primera celda del mes (B7) tendrá la fórmula =SI(DIASEM(B5)=2;B5;””). Como puedes ver es un poco más complicada pero fácil de entender. En realidad son dos fórmulas que hacen lo siguiente: si el primer día de la semana del este mes es lunes pongo la fecha del día, si no lo es no muestro nada. La función DIASEM() nos da el número que ocupa dentro de la semana una fecha concreta, en este caso buscamos el dos porque el lunes es el segundo día de la semana anglosajona. La función SI() comprueba el primer valor, si es cierto muestra el segundo valor y si no lo es muestra el tercero.

Pasamos al segundo día de la primera semana. Esta fórmula es más complicada que la anterior, puesto que tiene dos condiciones anidadas. En la celda C7 pondremos la fórmula =SI(B7<>””;B7+1;SI(DIASEM(B5)=3;B5;””)). ¿Qué significa? Si el día anterior (B7) no está vacío añade un día más, si lo está comprueba si el martes es el primer día del mes. ¿A que ahora parece más sencillo? Pues sólo queda repetir la fórmula hasta la celda H7 cambiando la celda del día anterior y el número de día de la semana.

En la siguientes semanas la fórmula es mucho más sencilla. No hay que comprobar nada ya que el primer día del mes está en la primera semana, así que sólo hay que sumar uno al día anterior. Pondremos =H7+1 en el lunes (B8), =B8+1 en el martes y así hasta la cuarta semana.

En las dos últimas semanas la fórmula será =SI(H10<fin.mes(b5;0);h10+1;””), es decir, si el día anterior es menor que último dia del mes añado uno, si no la celda queda vacía. En este caso utilizamos la función FIN.MES(), muy parecida a DIASEM() pero añade el parámetro Número de mes que en este caso es cero.Ahora copia la formula en le resto de días hasta completar el mes con 6 semanas.

Último paso: terminando el calendario

Solo quedan 11 meses para terminar el calendario. Pero no te preocupes, no tendrás que repetir todo el proceso anterior. Basta con copiar el mes de enero. Como hemos utilizado referencias relativas en las fórmulas, excel mantiene esa relación. Lo único que tendrás que hacer es cambiar el número de mes en la formula del título. Para el mes de febrero utiliza esta fórmula =FECHA($B$3;2;1). Como puedes ver la celda B3 está precedida del signo $, lo que convierte esta referencia en absoluta y la mantendrá en cada copia.

Para terminar puedes darle formato al calendario cambiando fuentes y colores, combinando celdas y modificando tamaños. Como puedes ver excel es una herramienta sumamente útil de la que puedes sacar mucho rendimiento. Si este tutorial te ha sabido a poco échale un vistazo a nuestro curso online de Excel.

Descarga el calendario de ejemplo: Calendario perpetuo

 

Publicado por Laura Vidal Departamento de Comunicación de V&Z Formación

Deja un comentario

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