Calculadora Horas Anuales Trabajadas

Seguro te has preguntando muchas veces cuántas horas trabajas al año y si cumples con lo establecido con el convenio que tengas...
Hoy explicaremos como construir una calculadora de horas anuales trabajadas.

Necesitaremos pocas cosas:
1-Tabla con los 14 festivos anuales (lo que tengas en tu convenio reconocidos).
2-Tabla con los 365/366 días del año.
3-Tabla con el número de horas a trabajar por semanas.

haz clic en la imagen

Para el ejemplo tomaré los 14 festivos anuales de Madrid para el 2018.
En una Tabla que llamaré: 'TblFestivos'

Una segunda tabla, que llamaré 'TblTipos', que recogerá la distribución por semanas de las horas trabajadas por cada día (lunes, martes, etc.), según semanas:

Esta tabla es fundamental para nuestra calculadora.
Por lo que debemos verificar algunos puntos:
1- el primer campo 'Desde' debe estar ordenado en sentido ascendente
2- NO es posible solapar fechas en los distintos periodos definidos (i.e., las fechas deben ser un continuo entre intervalos).

La tercera y última tabla nos determinará día por día, de todo el año, cuantas horas hemos trabajado.
Llamaremos a la tabla 'Tbl_2018'.

Comenzaremos listando la serie de fechas desde el 1/1/2018 hasta el 31/12/2018 en el campo 'Días'.
Este campo no tiene fórmula alguna...

El resto de campos se componen de fórmulas diferentes que aportan algo más de información.

Campo 'Día_semana':
insertamos la fórmula:
=ELEGIR(DIASEM([@Dias];2);"lun";"mar";"mie";"jue";"vie";"sab";"dom")
que nos permite convertir en texto el día de la semana para los 365 días del año.

Este campo se empleará posteriormente para localizar las horas de la semana en la 'TblTipos'.

Campo 'Tipo Jornada':
con la fórmula:
=BUSCARV([@Dias];TblTipos[[#Todo];[Desde]:[Tipo]];3;1)

que nos retornará, de manera informativa, a qué tipo de semana corresponde cada día del año a partir de la 'TblTipos'.

Campo 'Lab/No lab':
con la fórmula:
=SI(O(DIASEM([@Dias];2)>=6;NO(ESERROR(BUSCARV([@Dias];TblFestivos[Festivos];1;0))));"No laborable";"laborable")
con esta fórmula condicional verificamos los dos casos en que un día del año es 'no laborable', o bien que sea sábado o domingo:
DIASEM([@Dias];2)>=6
o bien que esté entre los 14 festivos de la tabla 'TblFestivos'
NO(ESERROR(BUSCARV([@Dias];TblFestivos[Festivos];1;0)))

si se cumple alguna de las dos condiciones el día será 'No laborable', en caso contrario 'laborable'.

Finalmente, el último campo 'Horas', con el que asignaremos las horas determinadas para cada día del año.
Insertamos la fórmula:
=SI([@[Lab/No Lab]]="laborable";SUMAR.SI.CONJUNTO(INDIRECTO("TblTipos["&[@[Día_Semana]]&"]");TblTipos[Desde];"<="&[@Dias];TblTipos[Hasta];">="&[@Dias]);0)

Donde condicionamos que si el día fuera 'laborable' recuperaríamos, según fechas y qué día de la semana fuera, el número de horas correspondiente... en cualquier otro caso devolvemos cero horas trabajadas.

Como se observa para recuperar las horas del día a partir de la 'TblTipos' empleamos la función SUMAR.SI.CONJUNTO, ya que se dan las condiciones necesarias:
1- el dato a recuperar es numérico (se puede 'sumar')
2- NO se solapan fechas en los distintos periodos definidos.

Por otra parte el rango a sumar se genera con la función INDIRECTO, que nos aporta el dinamismo necesario para recuperar el dato de una columna u otra...

Solo nos queda sumar el campo de las 'Horas'.
Así en la celda F1 añadimos:
=SUMA(Tbl_2018[Horas])

dando en mi ejemplo un total de 1.764 horas al año trabajadas..
Si se compara con el convenio de donde se han tomado los datos, que indica que el máximo de horas es de 1.765 horas, vemos que se ajusta perfectamente a un calendario de una persona a jornada completa.

Otro caso sería si tuviéramos jornadas parciales ;-)

Salvo opinión en contra de algún experto laboralista, esta calculadora nos permitirá saber si cumplimos o no con la legislación laboral actual.

Espero te sirva.