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

Sorpresas que te da la EXCEL 2010 con algunas funciones.

Excel 2010 viene como los huevos Kinder, con sorpresa.

Si tienes problemas con las algunas funciones, no pierdas el tiempo, lee este post y descárgate el glosario de funciones Excel 2010, que he preparado.

La primera en la frente, nada más descargar la nueva Excel 2010, y empezar a trabajar con las hojas me di cuenta que no me reconocía una función tan popular como la BUSCARV, en ese momento como andaba mal de tiempo, lo sustituí por COINCIDIR + INDICE, pero me quede con la mosca detrás de la oreja, y pensando mal, pensando en un oscuro interés de los desarrolladores de Excel, de permitirte descargar gratis la nueva versión por un tiempo y sin todas las funciones. Me volví loco, ya estaba a punto de arrancarme las venas a mordiscos, en los foros todavía nadie hablaba del asunto, cuando me di cuenta, que la función en cuestión BUSCARV, que tenía en ficheros anteriores, al abrirlos en Excel 2010, me los traducía por CONSULTARV, tire del hilo y pude comprobar que además de esta función hay casi 25 que han cambiado de nombre.

Si miras en la ayuda de Excel 2010, todavía no está pulida, las funciones viene desordenadas, ya que el orden corresponde a los nombre en inglés de las funciones, cuando profundizas en una función los parámetro y la sintaxis de la misma, está todavía en inglés. Y algunos de los nombres asignados a las funciones no responden con la sintaxis que tienes que utilizar, o en el listado de funciones de informa de algunas que no son operativas o no son compatibles, o no funcionan ni en inglés, ni en castellano ni funciona la ayuda. Paciencia, es una versión beta, que esperemos que la mejoren más delante, eso espero, porque sigo apostando por la 2010.

Siempre he tenido a mano un glosario de funciones y comandos, porque alguna vez te pasa que te enredas con la ayuda de Excel, y en vez de aclararte cosas te las lía, Se llama "glosarioALL", no recuerdo de donde lo baje ni quien lo hizo, pero que me ha venido siempre bien tenerlo a mano. Por ello he actualizado dicho glosario con las nuevas funciones de EXCEL 2010, los cambios de nomenclatura y las equivalencias de las funciones entre 2010-2007 y 2003. Así como la traducción a otros idiomas, algo que viene muy bien cuando lees algunos artículos.


Notas del libro: Glosario Excel 2010. (Tiene dos hojas: "EXCEL 2010" y "glosario 2003", ambas hojas tiene filtros automáticos para facilitar la búsqueda, y viene sin macros).

• 
  
  
  
  Hoja "EXCEL 2010" encontraras:
  
  • Categoría a la que pertenece la función
    
  • Función 2010 tal y como la llama la ayuda de Excel. Con un hipervínculo para acceder a la ayuda de esa función.
    
  • Excel real 2010 la sintaxis real que has de utilizar.
    
  • Descripción de la función
    
  • Parámetros en inglés: breve descripción de la sintaxis y parámetros de la función.
    
  • Nombre de la función en Excel 2007.
    
  • Nombre que también utiliza en la ayuda la Excel 2007. (Un agente doble??).
    
  • Funciones en EXCEL 2003.
    
  • En la fila 1 están el número de funciones que hay, o más bien las que tengo contabilizadas, y que han pasado de 2003: 333, a la versión 2007: 347 y por fin en la versión 2010. 408. No es para tirar cohetes, ya que muchas de las nuevas funciones son para mantener la compatibilidad con las antiguas versiones. No contabilizo las dobles funciones de texto tipo DERECHA y DERECHAB, para alfabetos especiales. También hay un enlace mediante un hipervínculo a la relación oficial de todas las funciones de la Excel 2010.
    
  
  
  
  
• 
  
  
  
  
  En la hoja "Glosario 2003."
  
  • Tienes las funciones y comandos, de las versiones estándar o de complementos que debes añadir en 12 idiomas,: inglés, francés, holandés, alemán, italiano, portugués, sueco, danés, noruego, ruso, etc.
    
  
  
  
  

Y hablando de funciones no viene nada mal cargar el módulo de "morenfunc" con una gran cantidad de funciones extras, gratis, y muy útiles, puedes descargarlas en http://xcell05.free.fr/morefunc/french/, te lo recomiendo, me ha venido muy útil.

Puedes descargar el fichero de GLOSARIO EXCEL 2010 desde la web www.flashforward-financiero.es, no te preocupes si apareces en otra página, estoy poniendo en ese sitio todos los ficheros.  Descargar la hoja de cálculo 2010 CalendarioPuedes descargar el fichero en Glosario excel 2010; y para veriones anteriores Glosario excel 2010 v 2003.

Espero que te sirva.

También esta publicado en http://www.flashforward-financiero.es/index.php/excel

Un calendario para empezar el año 2010 con muchos proyectos

Un regalo a todos nuestros lectores por el nuevo año 2010  - una plantilla excel para calendario 2010 gratis,

FlashForward-Financiero te pone el calendario, tú los proyectos.

Unas cuantas plantillas de calendarios y plannings para planificar y programar un duro año. Si estás leyendo estas líneas es que has sobrevivido al 2009, es que tenemos coraje, inquietudes, proyectos y sueños para encarar este duro 2010. Así que ánimo empieza a llenar de notas los calendarios, y de seguido algunas aclaraciones sobre la hoja, en Excel 2010:

• La primera hoja llamada "01 Año" muestra el calendario para el año entero, con el número de semanas, y los días festivos, fiestas nacionales, de la Comunidad y las locales propias para Pamplona, Navarra. El espacio superior, puedes colocar fotos, tu objetivo anual, y si lo prefieres borrar las imágenes y ocultar las líneas para que desparezca ese espacio.
  
• La siguiente hoja "02 Calend.Horizontal" es un calendario compacto, del formato de una línea por mes, es una manera de imprimir un calendario de bolsillo, para la agenda, o para sustituir en algún soporte de un antiguo calendario que le hayas cogido cariño, vamos para lo que te puedas imaginar.
  
• La hoja "03 Planning semanal" es un calendario compacto y vertical, donde tienes a la vista todo el año agrupado por semanas y por meses, puedes visualizar el año en pequeños bloques o por meses. Se calcula automáticamente con los cambios que realices en la celda C1 de la hoja "01 Año", realmente depende totalmente de esa hoja, no hay magia en estas celdas, lo único digno de mención en esta hoja son los formatos condicionales. Para usar este calendario basta con rodear la fecha de inicio de una tarea con un círculo, con una línea hacia el lado derecho, donde puede apuntar lo que significa. Con tanto espacio en blanco disponible a la derecha, hay suficiente espacio para poner notas. Si he acabado, hago una versión limpia en una otra hoja de papel, o entro las fechas directamente en esta hoja de cálculo.
  
• "La Hoja "04 Semana a la vista", es un calendario de una semana a la vista, que puede servir como guía de planificarse una semana, y de traducir esos objetivos anuales o mensuales en etapas, tareas y reuniones que lo hagan conseguible. Funciona cambiando el número de semana en la celda R3, imprímela y la puedes tener encima de tu mesa o como almohadilla al ratón o debajo del teclado, o en el cuaderno de notas, en un sitio bien visible, que te recuerde tus prioridades.
• Por último esta la hoja "KK", donde se recogen algunas tablas necesarias para calcular los calendarios:
• Fiestas laborales en España, las propias de la Comunidad Foral de Navarra, y la fecha de los Sanfermines en Pamplona, son los propios de este año, tiene un hipervínculo al Boletín Oficial del Estado BOE en que se publicó las propias de este año 2010. Para años sucesivos basta con añadir en las celdas siguientes inferiores las fechas de otro año.
• Las fases lunares, hay que ponerlas a capón, no he encontrado un algoritmo que nos calcule las fases lunares, si alguien lo conoce por favor mándame un email, o deja tu comentario.
• El santoral, este sirve para siempre, es un detalle de cortesía cuando te acuerdas del santo de algún amigo, que normalmente no lo sabe ni el mismo.

Descargar el "Calendario 2010"

La descarga se realiza desde la web www.flashforward-financiero.es, no te preocupes si apareces en otra página, estoy poniendo en ese sitio todos los ficheros.  Descargar la hoja de cálculo 2010 Calendario
para Excel 2007 y +, 2010 | Excel 2003
En Excel 2003, no te aseguro que funcionen todos los formatos condicionales, ¿has pensado en cambiarte al Excel 2010?, y por qué no?. Haz un comentario.



¿Cómo funciona el calendario, cómo son las tripas?

Notas de cómo he planteado el fichero "Calendario 2010". El archivo está desprotegido, así que ¡ánimo, a enredar!.

• Para generar un calendario nuevo basta con cambiar la celda C1 de la hoja , lo que necesitamos saber el año. Digamos que el año está en la celda C1 de la hoja "01 Año", desde esa celda se cambia todos los calendarios.
• Excel guarda las fechas como un número que empieza en el 1 de enero de 1900, estos números son correlativos como los días, asi que las fechas del calendario es el numero anterior +1, la celda anterior+1, y con el formato puedes poner, dd-mm-aa, dd-aaaa, etc. Yo he optado por el formato "d", aparece sólo el número del día y oculta el resto de la información: mes y año. ¿Pero cómo y cuándo arranco el calendario, cuál debe ser el primer día? La clave está en saber desde que semana tiene que empezar el calendario, para ello, calculamos el primer día del año, y mediante la función DIASEM, averiguamos que día de la semana es (con la opción 3, la formula devuelve valores de 0 a 6), lo restamos del primer día del año, y ya sabemos cuál es el último lunes del año, y la fecha de la primera semana de nuestro calendario.

En la celda B12 =+FECHA(C1;1;1)-DIASEM(FECHA(C1;1;1);3)

• Este proceso lo volvemos a realizar en la Hoja "Calend.Horizontal", donde se muestra un planing de todos los días del año, con el formato de una línea por mes. El proceso es el mismo, tenemos que calcular el primer y/o último lunes antes de empezar ese mes.

En la celda C7 =+FECHA('01 Año'!$C$1;MES(B6)+1;1)-DIASEM(+FECHA('01 Año'!$C$1;MES(B6)+1;1)-1;2)

• A partir de aquí, el resto de la magia es el formato condicional, ocultan los días que están en el recuadro o en la línea del mes y no son de ese mes.
• Días festivos
  
• Resaltar sábado y domingos.
  
• Separar con una línea dos meses.
  
• Etc…
• ¡Eso es todo!

Siga adelante y descargar el "calendario 2010" libre de la plantilla. El archivo está desbloqueado. Así que hurgar las fórmulas y ver cómo funciona.
El artículo también esta en la sección de EXCEL de flasforward-financiero.es