Excel - Compensar horas en computo bimensual con un maximo de 8

Compensar horas en computo bimensual con un maximo de 8

Publicado por Edu (10 intervenciones) el 05/09/2023 21:20:31

En una empresa tenemos.
Mes_1 que hemos realizado 14.70 horas extras.
Mes_2 que hemos realizado -12.40 horas negativas.
En el computo bimensual la empresa solo puede restarnos 8 horas negativas.
El problema que tengo es como lo resuelvo con una formula que automáticamente me de el resultado correcto.
Es decir si de 14.7 - 8 = 6.7 esto es la lógica pero aplicarlo a la excel ya es otro tema. Estaría muy agradecido que pudieran ayudarme. Gracias de antemano.

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 2

Val: 4.908

Compensar horas en computo bimensual con un maximo de 8

Publicado por Antoni Masana (2540 intervenciones) el 05/09/2023 23:31:04

Sube un libro con unos cuantos casos y explica que se ha de hacer en cada uno de ellos.
Ten en cuenta que no tenemos ni idea de lo que haces.

Saludos.
\\//_

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Compensar horas en computo bimensual con un maximo de 8

Publicado por Eduardo (10 intervenciones) el 06/09/2023 10:58:07

Muchas gracias por su interés, le he adjuntado un libro de excel con unos cuantos ejemplos.
Lo que deseo es encontrar la formula adecuada para que me realice el calculo de una manera automática, a grandes rasgos le puedo decir que en un mes puedo tener horas positivas o negativas , por el computo bimensual, un mes tengo horas positivas y en el siguiente horas negativas y se compensan de un mes a otro, la empresa puede compensar las horas negativas con un máximo de 8 horas las restantes las asume.
Gracias de antemano.

Trabajo.ods.zip(38,2 KB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Compensar horas en computo bimensual con un maximo de 8

Publicado por Antoni Masana (2540 intervenciones) el 06/09/2023 11:55:31

Hay varias cosas que me confunden.

Los valores de la columna F están en formato decimal y no en formato hora. En la columna G te pongo como debería calcular las horas.

Captura_1

Los cálculos deben seguir el mismo criterio.

Este es un cuadro resumido de la parte de la formula (celda amarilla) ¿Como se tiene que rellenar?
No entiendo que datos hay en cada columna.

Captura_2

Saludos.
\\//_

Valora esta respuesta

Comentar

Compensar horas en computo bimensual con un maximo de 8

Publicado por Eduardo (10 intervenciones) el 07/09/2023 09:32:34

Buenos días:
Las columnas D y E están en formato hora; la columna F esta en formato Centesimal o decimal ( Así calcula la empresa las horas de las jornadas diarias). La casilla H34 son las jornadas que se tienen que realizar en el mes que esta en el formato centesimal.
La casilla F40 es el resultado de la suma de todos los días de la columna F menos H34.
La casilla F41 es donde he de poner la formula en centesimal.
Si necesitas resolver cualquier duda no lo dudes en preguntar.
Gracias de antemano.

Trabajo.ods.zip(38,2 KB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Compensar horas en computo bimensual con un maximo de 8

Publicado por Antoni Masana (2540 intervenciones) el 07/09/2023 14:21:19

En la columna F debería estar esta fórmula:

=REDONDEAR((E3-D3)*24;2)

Me falta entender este cuadro;

+-------+--------------+------------+-------------+

|       | Mes anterior | Mes actual | A compensar |

+-------+--------------+------------+-------------+

| Hoja1 |            0 |      11,97 |             |

+-------+--------------+------------+-------------+

| Hoja2 |        11,97 |     -18,98 |             |

+-------+--------------+------------+-------------+

| Hoja3 |              |       4,95 |             |

+-------+--------------+------------+-------------+

| Hoja4 |       -19,95 |      -3,22 |             |

+-------+--------------+------------+-------------+

| Hoja5 |              |       5,28 |             |

+-------+--------------+------------+-------------+

Mes anterior: Celda F39 - ¿De donde sale este dato?

Mes actual: Celda F40 - Es la suma del rango F3:F33 menos el valor de H34 --- =SUMA(F3:F33)-H34

A Compensar: Celda F41 - ¿Que tiene que calcular la fórmula? y ¿Cual seria el resultado en cada hoja?

Saludos.
\\//_

Valora esta respuesta

Comentar

Compensar horas en computo bimensual con un maximo de 8

Publicado por Eduardo (10 intervenciones) el 08/09/2023 10:05:47

Buenos días: Antoni Masana, no me canso de darte las gracias.
En cuanto al tema que nos ocupa:

Mes anterior: Celda F39.Esto es porque estas en la hoja 2 y ese dato de F39, sale de la hoja 1 de la suma de la columna F menos H34( que son las horas que tenemos que realizar mensualmente).

A compensar: Celda F41: Esta es la cuestión , es decir tiene que realizar la siguiente operaciones la suma del mes anterior (F39) menos el del actual y calcular Computo bimensual, con un máximo de horas de 8 negativas ( En la hoja 2 son 11.98 - 8) porque tenemos establecido que solo nos pueden descontar 8. (Si en lugar de 18.98 fuese 6.95 horas negativas, nos descontarían 6.95, no las 8 que tenemos como tope máximo).

El resultado en este caso sin utilizar formulas seria 11.97-8(Son 8 por ser el máximo a descontar, si fuese cualquier otra cantidad menor de 8 seria la que se descontaría).

Saludos.

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Compensar horas en computo bimensual con un maximo de 8

Publicado por Antoni Masana (2540 intervenciones) el 08/09/2023 18:05:51

Los datos que tengo son estos.

+-------+----------+-----------+-------------+--------------+------------+-------------+

|       |    H34   |   F3:F33  | Celda: H36  |  Celda F39   | Celda: F40 |  Celda: F41 |

+-------+----------+-----------+-------------+--------------+------------+-------------+

|       | convenio |     SUMA  | Horas Extra | Mes anterior | Mes actual | A compensar |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja1 |   165,30 |    177,27 |       11,97 |            0 |      11,97 |             |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja2 |   172,56 |    153,58 |      -18,98 |              |     -18,98 |             |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja3 |   165,30 |    170,25 |        4,95 |              |       4,95 |             |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja4 |   165,30 |    162,08 |       -3,22 |              |      -3,22 |             |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja5 |   165,30 |    170,58 |        5,28 |              |       5,28 |             |

+-------+----------+-----------+-------------+--------------+------------+-------------+

En la celda F39 debería ser esto pero lo supongo y no tengo ninguna certeza:

+-------+----------+-----------+-------------+--------------+------------+-------------+

|       |    H34   |   F3:F33  | Celda: H36  |  Celda F39   | Celda: F40 |  Celda: F41 |

+-------+----------+-----------+-------------+--------------+------------+-------------+

|       | convenio |     SUMA  | Horas Extra | Mes anterior | Mes actual | A compensar |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja1 |   165,30 |    177,27 |       11,97 |            0 |      11,97 |             |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja2 |   172,56 |    153,58 |      -18,98 |        11,97 |     -18,98 |             |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja3 |   165,30 |    170,25 |        4,95 |       -18,98 |       4,95 |             |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja4 |   165,30 |    162,08 |       -3,22 |         4,95 |      -3,22 |             |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja5 |   165,30 |    170,58 |        5,28 |        -3,22 |       5,28 |             |

+-------+----------+-----------+-------------+--------------+------------+-------------+

El siguiente paso es sumar las horas de las celdas F39 y F40 y el resultado es este:

+-------+----------+-----------+-------------+--------------+------------+-------------+

|       |    H34   |   F3:F33  | Celda: H36  |  Celda F39   | Celda: F40 |  Celda: F41 |

+-------+----------+-----------+-------------+--------------+------------+-------------+

|       | convenio |     SUMA  | Horas Extra | Mes anterior | Mes actual | A compensar |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja1 |   165,30 |    177,27 |       11,97 |            0 |      11,97 |             |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja2 |   172,56 |    153,58 |      -18,98 |        11,97 |     -18,98 |       -7,01 |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja3 |   165,30 |    170,25 |        4,95 |       -18,98 |       4,95 |      -14,03 |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja4 |   165,30 |    162,08 |       -3,22 |         4,95 |      -3,22 |        1,73 |

+-------+----------+-----------+-------------+--------------+------------+-------------+

| Hoja5 |   165,30 |    170,58 |        5,28 |        -3,22 |       5,28 |        2,06 |

+-------+----------+-----------+-------------+--------------+------------+-------------+

Hay varias cosas que no entiendo:

- El computo es bimensual: eso quiere decir:

Enero - Febrero
Febrero - Marzo
Marzo - Abril
Abril - Mayo

O esto:

Enero - Febrero
Marzo - Abril
Mayo - Junio

- Las horas extras: Se considera horas extras cuando el valor H36 es positivo

No entiendo la explicación:

A compensar: Celda F41: Esta es la cuestión , es decir tiene que realizar la siguiente operaciones la suma del mes anterior (F39) menos el del actual y calcular Computo bimensual, con un máximo de horas de 8 negativas ( En la hoja 2 son 11.98 - 8 {a}) porque tenemos establecido que solo nos pueden descontar 8. (Si en lugar de 18.98 fuese 6.95 horas negativas {b}, nos descontarían 6.95, no las 8 que tenemos como tope máximo).

El resultado en este caso sin utilizar formulas seria 11.97-8(Son 8 por ser el máximo a descontar, si fuese cualquier otra cantidad menor de 8 seria la que se descontaría)

{a} - ¿De donde salen el 11,98 de la hoja2?

{b} - Esto aun lo entiendo menos. 18,98 es un valor negativo, quiere decir que la suma de horas de la columna F es menor que las horas de convenio. ¿Horas extra? Entonce las horas de la hoja 1 ¿Que son?.

Pon unos casos prácticos.

Saludos,.
\\//_

Valora esta respuesta

Comentar

Compensar horas en computo bimensual con un maximo de 8

Publicado por Eduardo (10 intervenciones) el 08/09/2023 20:56:58

Buenas tardes.
El computo es bimensual es:
Enero - Febrero
Febrero - Marzo
Marzo - Abril
Abril - Mayo y Etc.
Las horas extras: Se considera horas extras cuando el valor H36 es positivo: Si.

La celda F41 es donde estaría la formula que realizara la operación , con los datos de F39 que es del mes anterior - F40 seria en este caso el resultado de 9.85, teniendo en cuenta que no supera el tope máximo que tenemos de 8 Horas negativas. Ejemplos:

Si en el mes 1 realizamos 18 Horas Positivas, En el mes 2 hacemos 10 horas negativas el resultado daría: 18-8( Porque se hicieran mas horas Negativas el máximo a descontar seria 8) el resultado en este caso seria 10 horas positivas.

Ejemplo 2:

En el mes 1 hacemos 18 horas Positivas. En el mes 2 tenemos 6.56 horas negativas(Como no superamos las 8 horas negativas solo se restara 6.56) y el resultado para este caso seria 11.44.

Entonce las horas de la hoja 1 ¿Que son?.
En este caso son horas positivas o Extras.

¿De donde salen el 11,98 de la hoja2?
Sale de la suma de la columna F, de la hoja1 menos H34 de la hoja 1.

Esto aun lo entiendo menos. 18,98 es un valor negativo, quiere decir que la suma de horas de la columna F es menor que las horas de convenio.

Si es decir si la columna F Suma 177.27 horas y en el convenio tenemos 165.30 son horas positivas,Horas extra, si fuese al contrario que en la columna F sumase 165.30 y las del convenio 177.27 el resultado seria 11.97 Horas negativas.

Saludos.

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Compensar horas en computo bimensual con un maximo de 8

Publicado por Antoni Masana (2540 intervenciones) el 09/09/2023 11:06:00

Perdona pero no entiendo nada de lo que me explicas.

Por ejemplo en esta frase:

La celda F41 es donde estaría la formula que realizara la operación , con los datos de F39 que es del mes anterior - F40 seria en este caso el resultado de 9.85, teniendo en cuenta que no supera el tope máximo que tenemos de 8 Horas negativas.

¿De donde salen 9.85?

En este ejemplo no entiendo el cálculo:

Si en el mes 1 realizamos 18 Horas Positivas, En el mes 2 hacemos 10 horas negativas el resultado daría: 18-8( Porque se hicieran mas horas Negativas el máximo a descontar seria 8) el resultado en este caso seria 10 horas positivas.

Te pongo una fórmula que soluciona este caso:

S(Mes2 < -8 ; Mes1 - 8 ; Mes1 - Mes2 )

Si el ejemplo fuese con los meses invertidos, es decir:

Si en el mes 1 realizamos 10 Horas negativas, En el mes 2 hacemos 18 horas positivas el resultado daría: 18-8( Porque se hicieran mas horas Negativas el máximo a descontar seria 8) el resultado en este caso seria 10 horas positivas.

Te pongo una fórmula que soluciona este caso:

S(Mes1 < -8 ; Mes2 - 8 ; Mes2 - Mes1 )

Pero esto no abarca todos los rangos:
¿Que pasa si los dos son positivos?
¿Que pasa si los dos son negativos?
¿Que pasa si el valor absoluto del positivo es menor que el valor absoluto del negativo? Ejemplo 7 y -10 y en este: 5 y -7
¿Que pasa si uno de los valores es CERO?

Saludos.
\\//_

Valora esta respuesta

Comentar

Compensar horas en computo bimensual con un maximo de 8

Publicado por Eduardo (10 intervenciones) el 10/09/2023 10:43:25

Buenos días Antoni muchas gracias por tu paciencia.

¿De donde salen 9.85?
Sale de la hoja2 celda F39 -F40. Como tenemos que en el mes anterior tenemos 11.97 y este actual hemos hecho -2.12 negativas se restan de las 11.97 con el resultado de 9.85 horas extras o positivas.

En este ejemplo no entiendo el cálculo:
Si en el mes 1 realizamos 18 Horas Positivas, En el mes 2 hacemos 10 horas negativas el resultado daría: 18-8( Porque se hicieran mas horas Negativas el máximo a descontar seria 8) el resultado en este caso seria 10 horas positivas.

Con esto quiero decir que hipotéticamente en un mes hacemos horas Positivas y otro negativas, se restarían un máximo 8 siempre que fueran mas de 8, horas negativas, si fuese menor de 8 negativas, seria la cantidad que fuese.

Si el ejemplo fuese con los meses invertidos:
Se restaría dando el resultado de 8 horas positivas.

¿Que pasa si los dos son positivos?
Todos los meses tenemos horas que tenemos que realizar por convenio y se pudiera dar el caso que el resultado fuese el mismo en este caso seria 0; Si por el contrario te refieres que en un mes tenemos horas positivas y en el siguiente horas positivas se sumarían.

¿Que pasa si los dos son negativos?
Se sumarían y se restarían del siguiente si fuesen positivas.

¿Que pasa si el valor absoluto del positivo es menor que el valor absoluto del negativo? Ejemplo 7 y -10 y en este: 5 y -7
Esto no tengo claro lo que me quieres decir; vale lo traslado a los meses seria para 7-10, Para este caso seria 7-8=-1 (8 es el máximo de horas que pueden restarnos) y para 5-7= -2

¿Que pasa si uno de los valores es CERO? si son 0 son cero (nunca se nos hadado este caso)

No se si te he dado mas luz o oscuridad.

No pensé que para unos cálculos aparentemente fáciles fuesen tan complicados, es por ello que lamento esta complicación.
Gracias de antemano.
Saludos .
Edu.

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Compensar horas en computo bimensual con un maximo de 8

Publicado por Antoni Masana (2540 intervenciones) el 10/09/2023 14:07:35

Buenos días Eduardo.

ES difícil entenderse por correo, sobre todo por que al explicar algo se nos olvida que algo que tenemos asumido de forma natural el oyente no tiene ningún conocimiento. Me he encontrado consultas en la web que se explican como lo haría a un colega del despacho que sabe de lo que se está ablando, el que está al otro lado de pantalla no sabe nade de lo que está haciendo.

Pero no solo pasa en el foro, también lo he visto estando en el despacho del cliente que omite información por que o la da por sabida, ya me didas que se yo de lo que hacen en ese despacho, o porque no la cree relevante y después vienen las sorpresas. Lo que pasa es que en persona la comunicación es más fluida.

Ahora vamos al tema.

Para hacer una fórmula de estas características se deben tener en cuenta todas las combinaciones posibles, probables e improbables, si ya imposibles también porque estas acaban saliendo.

Los dos primeros casos:

- Dos positivos y dos negativos: Se suman

=SI( O( Y( F39 >= 0 ; F40 >= 0 ); Y( F39 <= 0 ; F40 <=0 ) ) ; F39 + F40 ;

Tercer caso:

Mes1 +
Mes2 –
Si en el Mes2 hay mas de 8 horas le resto 8 al Mes1 en caso contrario Mes1 – Mes2

SI( Y( F39 > 0 ; F40 < 0 ) ; SI( F40 < -8 ; F39 – 8 ; F39 -F40 );

Cuarto caso:
Mes1 -
Mes2 +
Si en el Mes1 hay mas de 8 horas le resto 8 al Mes2 en caso contrario Mes2 – Mes1

SI( Y( F39 < 0 ; F40 > 0 ) ; SI( F39 < -8 ; F40 – 8 ; F40 – F39 ))

La fórmula completa:

=SI( O( Y( F39 >= 0 ; F40 >= 0 ); Y( F39 <= 0 ; F40 <=0 ) ) ; F39 + F40 ;

 SI( Y( F39 > 0 ; F40 < 0 ) ; SI( F40 < -8 ; F39 - 8 ; F39 -F40 );

 SI( Y( F39 < 0 ; F40 > 0 ) ; SI( F39 < -8 ; F40 - 8 ; F40 - F39 ))))

La versión compacta:

=SI(O(Y(F39>=0;F40>=0);Y(F39<=0;F40<=0));F39+F40;SI(Y(F39>0;F40<0);SI(F40<-8;F39-8;F39-F40);SI(Y(F39<0;F40>0);SI(F39<-8;F40-8;F40-F39))))

A ver que tal esta la formula.

Saludos.
\\//_

Valora esta respuesta

Comentar

Compensar horas en computo bimensual con un maximo de 8

Publicado por Eduardo (10 intervenciones) el 15/09/2023 10:22:24

Buenos días Antoni Gracias por tu formula que funciona correctamente y realiza las funciones Tal cual te pedí, gracias.

Valora esta respuesta

Comentar

Conciliación entre cuentas bancarias y registros de caja.

Buscar y copiar pantallas de otro programa con VBA