domingo, 24 de enero de 2010

¿Cómo hacer un calendario laboral para el 2010, personalizado para cada trabajador de una empresa?

Gracias al comentario que me hizo Kris al post sobre el calendario 2010, en que me preguntaba cómo añadir más fiestas sin tener que retocar los rangos, me pico el reto de hacer lo que proponía, y me puse a perfeccionar un calendario, para ello me puse el objetivo de hacer un calendario laboral que me calcular la jornada anual de trabajo, es decir cómo convertir las horas anuales pactadas en el convenio anual, y plasmarlo en los días concretos, y como juntar eso con las vacaciones, los puentes etc.

El objeto de la plantilla es:
  1. Elabora el calendario laboral para una empresa determinada.
  2. Calcular la distribución de la jornada y el número de horas trabajadas por día, mes y año.
  3. Personalizar el cálculo para cada trabajador.
La información que se obtiene:
  1. Un planning anual donde queda recogidos los días de trabajo, de fiesta, los días de vacaciones, y las horas a trabajar, personalizado para cada trabajador.
  2. Calculo de la jornada real anual, horas trabajadas por un empleado.
  3. Calculo de los días pendientes de vacaciones.
  4. Horas a favor o en contra del trabajador por haber empleado más horas que las que marca el convenio laboral o no.
  5. Días de vacaciones.
Estructura y DESCARGAR FICHERO EXCEL GRATIS

La plantilla tiene 2 hojas, "Fechas clave" y "Plan año". La primera donde están los datos de fiestas, vacaciones, etc. Y la segunda esta el planning anual. la descarga se realiza en la web de www.flashforwar-financiero.es., tanto en formato DESCARGAR en EXCEL 2010 y 2007.

Los datos clave son:
  1. Jornada laboral anual marcada por el convenio que afecte a la empresa en cuestión.
  2. Los días de vacaciones que tenga el trabajador. 
  3. Si los sábados son laborales o no.
  4. El año en cuestión y en qué mes quieres empezar el calendario.
  5. Si la empresa marca unos días de vacaciones para toda la plantilla, y cuales son.
  6. Si hay horario de verano, y entre que fechas.
  7. La distribución de horas por días en el horario de verano y de invierno.
  8. Las fiestas no recuperables, nacionales, autonómicas y locales.
  9. El nombre del trabajador. 
Cómo usar la plantilla.

En la hoja de Fechas clave, a partir de las celdas E16:F16, introduce el nombre del trabajador y los días de vacaciones que le corresponde. Es importante que haya el mismo número de nombres de trabajador como fechas, cada fecha de vacaciones tiene que estar asignado a un trabajador, no dejes espacios en blanco.
Una vez que tengas todos los datos cargados puedes ir imprimiendo el calendario de trabajo anual personalizado de cada trabajador, modificando el nombre en la celda T2 de la hoja "Plan año"

"Las tripas de la hoja": fórmulas y funciones mas interesantes.

Los nombres de las tablas son dinámicos, es decir que se ajustan automáticamente a los datos que vayas introduciendo. Es importante que no se eliminen filas enteras, y si quieres eliminar celdas utiliza o borrar, o eliminar celdas desplazando hacia arriba o ordenando. Por ejemplo el rango de "festivos" lo hemos declarado como.
=DESREF('Fechas clave'!$A$16;0;0;CONTARA('Fechas clave'!$A:$A)-1;1)
En el administrador de nombres de rangos lo podrás comprobar.

En la hoja de "Plan año", las celdas y fórmulas mas interesantes son:
  1. Una vez seleccionado el mes en que se quiere empezar, los 11 meses consecutivos se calculan con la función =+FECHA.MES(D10;1), para que aparezca siempre el primer dia del mes siguiente.
  2. El día de la semana, es un combinación de dos funciones ELEGIR y DIASEM: =SI(ESNUMERO(D10);ELEGIR(DIASEM(D10;2);"L";"M";"X";"J";"V";"S";"D");"") 
  3. En la columna auxiliar se detemina que incidencia tiene ese día según la tabla que aperce en la celda K8 de la hoja "Fechas clave". Es una formula compuesta de diferentes condicinales anidados y de funciones de búsqueda y referencia, es una fórmula matricial. Pe. F10 es:   =SI(ESNUMERO(D10); SI(ESERROR(CONSULTAV(D10;tab_jor_reducida;2;FALSO))=VERDADERO;SI(ESERROR(COINCIDIR(D10;festivos;0)); SI(DIASEM(D10;2)=7; 2; SI(DIASEM(D10;2)=6; SI($N$2;3;0);SI( Y(D10>=vac_ini; D10<=vac_fin); 5; SI(ESERROR(COINCIDIR(MAYUSC($T$2)&D10; MAYUSC(lan_izena)&lan_egun; 0)); SI(Y(D10>=hv_fini;D10<=hv_ffin); 7; 8 ); 6)))); 4);9);1) 
  4. El cálculo de las horas de trabajo que corresponden a cada día utiliza la columna auxiliar y busca las horas que corresponden a cada incidencia. Pe. La celda G10 es:  SI( Y(D10>=vac_ini; D10<=vac_fin); 0; SI(ESERROR(COINCIDIR(MAYUSC($T$2)&D10; MAYUSC(lan_izena)&lan_egun; 0)); SI(Y(D10>=hv_fini;D10<=hv_ffin); INDICE('Fechas clave'!$J$6:$P$6;1;DIASEM(D10;2)); INDICE('Fechas clave'!$J$2:$P$2;1;DIASEM(D10;2)) ); 0)); 0)))  




  5. Los "coloricos o colorimes", son una combinación de formatos condicionales, hay una macro que los actualiza. Hay un botón para hacerla correr. Puedes verla con Alt+F11.





Espero que te sea útil, si tienes cualquier duda, sugerencia, crítica, mejora, haz un comentario o manda un email, con eso me doy por pagado.

10 comentarios:

  1. Te informo que el calendario en la versión 2003, no funciona el calculo de las horas.

    ResponderEliminar
  2. Iñigo,
    En anteriores post, comente que el diseño de estas hojas han sido elaboradas con excel 2010. Estos formatos los lee perfectamente el que tenga excel 2007, pero no los del 2003. En el proceso de conversión del modelo a la version 97-2003, hay algunas funcionalidades, (fórmulas y formatos condicionales) que desaparecen. Por esta razon veras errores al descargar la version excel 2003.En la versión 2010 esta probada y no da errores.
    Gracias por el aviso.

    ResponderEliminar
  3. Totxi, impresionante. ¡Qué dominio de los calendarios!
    una pregunta: ¿sabes cómo se hace un calendario académico, con las fechas de examenes, entragas de trabajos...?

    ResponderEliminar
  4. Desconozco tu E-mail, te adjunto esta dirección que estoy seguro que te gustara:
    http://www.mendikat.net/inicio.php

    Chimonco

    ResponderEliminar
  5. Luís (luismvs@mundo-r.com)10 de marzo de 2010, 12:29

    Realmente muy bueno. Voy a aprovechar tu proyecto y ampliarlo un poco para poder realizar un calendario laboral anual pero con jornadas de trabajo irregulares. Quisiera poder plasmar en un calendario anual cada día de trabajo en un color asociado a un horario o algo así. Quizás tú puedas ayudarme. Aun no lo tengo muy claro y acepto cualquier sugerencia de tu parte. Bueno, lo dicho, un proyecto magnífico. Muchas gracias Totxi. Luís.

    ResponderEliminar
  6. Lo tienes hecho para 2011 porque me parece muy util

    ResponderEliminar
  7. hola, la version que tengo en el trabajo es excel 2003. que s la que utilizamos.
    habria alguna forma de que las formulas o los formatos...se convirtieran para el 2003...

    ResponderEliminar
  8. Anonimo,
    Siento decirte, que no es posible en las versiones excel 2003, utilizar el modelo.
    La razon es que en las versiones 2003 o anteriores, la función SI, esta limitado a 8 anidaciones, "Si(... Si(....,", sólo desde la 2007 no hay esta limitación.
    Espero terminar una versión para el 2011 que resuelva este problema, estate atento.
    Gracias por seguir el blog y por tu comentario.
    Totxi

    ResponderEliminar
  9. Mar,
    El error que te da, es debido a la versión EXCEL 2007, no dispone de la función DIASLAB.ENTRE de la versión 2011. Te renvio el fichero que me has adjuntado corregido.
    Sobre que no te coge el día 29/12 como festivo, es debido a que se ha borrado el contenido de las celdas e35:f35. Que indican el límite de las fechas de vacaciones y que puede ser modificadas ampliándolo o reduciendo con el comando inserta celdas hacia abajo.
    Gracias por tus comentarios y saludos
    Totxi

    ResponderEliminar
  10. Hola, necesito hacer un claendario de planificación de diferentes eventos en excel, con distinta información y me gustaría que pudiera aparecer en modo calendario o al menos que pueda saber en un mismo mes, la cantidad de eventos que hay y los que se solapan en días. Adicionalmente a esa info, quiero otra como saber el responsable del proyecto y otra info que necesito. De momento lo he intentado con tablas dinámicas, pero la info que quiero mostrar no necesito que sea calculada (una condición de las tablas dinámicas). No sé si alguien podría indicarme algo o mandarme alguna hoja de excel que tenga algo similar. GRACIAS!!!!!

    ResponderEliminar

Puedes hacer un comentario