¿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.