martes, 17 de diciembre de 2013

Calendario laboral en Excel.

Por estas fechas siempre tenemos una necesidad (humana diría yo) de conocer el futuro... desde luego no será Excel quien nos de respuesta a tan antigua pregunta...
Pero lo que si podemos conseguir es conocer y disponer en Excel de un calendario laboral vistoso.. y si además conocemos de antemano las Festividades del año, incluso quedarán marcadas en rojo.


Aquí en España las festividades oficiales Nacionales y de las Comunidades autónomas aparecen más o menos por el mes de Noviembre (ver BOE núm 278 20-Nov-2013 Sec. I. Pág. 92699-92701). Pero servirá para cualquier parte del mundo.. bastará que conozcas los festivos oficiales de tu zona o país...
Puestos en antecedentes, pasemos a lo que nos interesa, cómo construir en Excel ese calendario laboral.


Diré que todo funcionará empleando fórmulas matriciales incorporadas en Nombres definidos, lo que facilita la comprensión; además utilizaremos los Formatos condicionales para marcar nuestras festividades. Y dejando atrás macros u otros procesos ya explicados en alguna entrada anterior (ver)

La primera parte del trabajo consiste en preformatear las celdas donde irán nuestros meses y días. Ver imagen siguiente:

Calendario laboral en Excel.



Comenzaremos mostrando los Nombres definidos que nos servirán para construir nuestro Calendario:
calendario =díasysemanas + PrimerDia - DIASEM(PrimerDia;2)
díasysemanas =COLUMNA(DESREF(INDIRECTO("$A$1");0;0;1;42))
Festivos =TblFestivos[Festivos del año]
PrimerDia =FECHA(CalendarioPerpetuo!$A$3;MES(CalendarioPerpetuo!$B7);1)

Calendario laboral en Excel.



Como vemos tenemos una Tabla llamada 'TblFestivos' que utilizaremos para los formatos condicionales.
Tenemos un nombre 'PrimerDia' que nos devuelve el primer día de cada mes, según la fila del rango B7:B18.
El nombre definido 'díasysemana' devuelve un rango virtual con valores de 1 al 42. El 42 no es un número al azar, es el resultado de 6 x 7, es decir seís semanas por siete días por semana. Con esto controlamos el máximo ancho posible, puesto que no hay posibilidad alguna de que un mes tenga más de seis semanas...
Por último con el nombre 'calendario', combinando ambos nombres definidos anteriores, tendremos la secuencia de los días del mes. Si contemplamos sólo la suma de
=díasysemanas + PrimerDia
para corregir y afianzar el comienzo correcto en el día de la semana adecuado, incorporamos la función
DIASEM(PrimerDia;2)
que nos dice el día de la semana en que comienza el primer día de cada mes...


Estamos en disposición de ejecutar nuestra matricial 'calendario' sobre nuestra plantilla, para ello seleccionaremos fila a fila los rangos correspondientes a cada mes, es decir, comenzaremos seleccionando, para enero, el rango D7:AS7, en D7 escribimos 'calendario' y ejecutamos matricialmente (presionando Ctrl+Mayusc+Enter).
Repetiremos la operación doce veces, una por mes.. para febrero, seleccionamos D8:AS8 y en D8 escribimos 'calendario' y ejecutamos matricialmente, etc. etc.


El resultado final sería (ya aplicado el formato condicional):



Para marcar los festivos (los que aparezcan en la tabla de Festividades) y los domingos del año en rojo; así como para que sólo aparezcan los días de cada mes en la fila correspondiente, aplicaríamos los siguientes formatos condicionales:

Calendario laboral en Excel.


Para aplicar los dos formatos condicionales seleccionaremos el rango D7:AS18 y con la celda D7 activa, construiremos las reglas:
1- para ocultar días de otros meses en las filas de cada mes:
=MES(D7)<>MES($B7)
2- para resaltar de rojo los domingos y festivos:
=O(D$6="D"; CONTAR.SI(Festivos; D7)>0)


Acabamos con un formato de celda personalizada para nuestro rango D7:AS18, con tipo: d

Calendario laboral en Excel.

4 comentarios:

  1. Se me es interesante este tema, pero creo que a la vez complicado...no lo entendi

    ResponderEliminar
    Respuestas
    1. Hola Richard,
      el asunto es crear los nombres definidos con las fórmulas indicadas.
      Sigue los pasos y aplica los formatos.

      De todas formas especifica qué no entiendes y quizá pueda aclarártelo.
      Un cordial saludo

      Eliminar
  2. Hola Ismael,
    Llegué a tu foro buscando información para trabajar con este tipo de fórmulas, ya que estoy modificando una plantilla de Excel ("Resumen del semestre") que permite ingresar clases, tareas pendientes entre otras cosas.
    Mi problema surge en la pestaña "Programación del semestre", esta pestaña viene configurada para visualizar tipo calendario solo 4 meses con los días de tareas pendientes marcados en rojo y las celdas con clases pintadas de celeste. Traté de agregar los 4 meses que faltan para completar mi año pero solo logré agregarlos sin que se marquen las tareas en rojo y las celdas pintadas con las clases están corridas.
    Si puedes y tienes tiempo de ayudarme te lo agradecería enormemente.
    Saludos y gracias de antemano.
    Sofía
    sofiamirandav@live.com

    ResponderEliminar
    Respuestas
    1. Hola Sofia,
      la clave de los colores está en los formatos condicionales, tendrás que ajustarlas también al nuevo rango...

      Saludos

      Eliminar