Si alguien puede darme algún tipo de ayuda sobre por qué tengo números negativos en lugar de 40, que es lo que debería ser, por favor ayuda. Estaría muy agradecido. ¡Gracias!
Respuesta1
Tiene correctamente seleccionado "Usar el sistema de datos 1904" para el libro, en la sección Archivo > Avanzado > "Al calcular este libro" (desplácese hacia abajo hasta el final).
Esto es necesario para permitir que los tiempos se calculen como negativos. (Intenta apagarlo y verás que los negativos se convierten solo en #)
Pero en B16 y C16, por ejemplo, la hora simplemente se introduce a las 18:00 y 01:00 sin ninguna fecha. De 01:00 a 18:00 es realmente un horario negativo. Aproximadamente -0,71 si se muestra como un número.
La solución es cambiar el cálculo de cada hora de inicio-finalización individual para tener en cuenta el paso de la medianoche. Entonces reemplace "C16-B16" con "IF(C16-B16<0,C16-B16+1,C16-B16)"
Deberías hacerlo para cada día de la semana, lo que hace que la fórmula sea bastante larga.
Considere agregar una columna adicional entre los días para mostrar las horas trabajadas ese día. Entonces el total sólo necesita sumar esas celdas.
Editar: ¡adecuado!
Edite nuevamente: debe usar +1 como en mi ejemplo, en lugar de +24 como en la publicación anterior, porque la unidad son días, no horas.
Edición final: una solución mucho más breve es reemplazar "C16-B16" por "MOD(C16-B16,1)". Esto funciona manteniendo sólo la fracción del tiempo. Con tiempos, un 1 decimal son 24 horas.
Respuesta2
La fórmula habitual para pasar de 24 es:
=EndTime - StartTime +(EndTime < StartTime)
Combinar todo eso en una fórmula, para sumar todos los días de la semana, es complicado pero factible. Requiere una fórmula ingresada en una matriz, ya que es necesario probar cada par individualmente.
La siguiente fórmula hace uso de su configuración en el sentido de que todos sus EndTimes están en columnas pares; y las horas de inicio están en columnas impares.
Tenga en cuenta que las dos matrices construidas se diferencian en una columna. En las versiones de Excel 2007+, puede probar directamente IMPAR/PAR con la función ISODD/ISEPAR.
Esta fórmula debe seringresado en matriz:
=SUM(
MOD(COLUMN($C16:$O16),2)*$C16:$O16-
(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16+
((MOD(COLUMN($C16:$O16),2)*$C16:$O16-(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16)<0))
*24
o, usando ISODD e ISEVEN:
=SUM(
ISODD(COLUMN($C16:$O16))*$C16:$O16-
ISEVEN(COLUMN($B16:$N16))*$B16:$N16+
((ISODD(COLUMN($C16:$O16))*$C16:$O16-ISEVEN(COLUMN($B16:$N16))*$B16:$N16)<0))
*24
O, incluso más corto, pero más difícil de entender, ya que usamos la función MOD para retener solo el componente fraccionario:
=SUM(
MOD(
ISODD(COLUMN($C16:$O16))*$C16:$O16-
ISEVEN(COLUMN($B16:$N16))*$B16:$N16,
1))
*24
Aentrar en matrizuna fórmula, después de ingresar la fórmula en la celda o barra de fórmulas, mantenga presionada Ctrl-Mayúsmientras golpeaingresar. Si hizo esto correctamente, Excel colocará llaves {...} alrededor de la fórmula.
Hice la fórmula para la línea que muestras en tu captura de pantalla. El resultado es 37.9833 usando tus números
Respuesta3
Tu problema es cuando pasas de la medianoche. Tomemos, por ejemplo, la primera fila los viernes, sábados y domingos. Cada día la hora de finalización son las 2:00 a.m. Por ejemplo, el viernes, cuando restas los tiempos, se toman las 6:00 p. m. del viernes menos las 2:00 a. m. del VIERNES, que es antes de las 6:00 p. m.
Para evitar lo negativo, pero aun así mantener el bonito aspecto visual de su gráfico, debe utilizar una declaración IF. Para calcular para el uso del viernes:
=IF(K3 < J3, K3 - J3 + 24, K3 - J3)
Hay tres secciones en la declaración IF.
(1) la condición
K3 < J3
Esto verifica si K3 (la hora de finalización) es menor que J3 (la hora de inicio). Si es así, devolvería un número negativo (la hora de las 2:00 a. m. es "menos que" las 6:00 p. m.).
(2) si la condición es verdadera
K3 - J3 + 24
Si la condición es verdadera, Excel usa esta ecuación. Lo escribí para sumar 24 para contrarrestar cualquier aspecto negativo que pudiera ocurrir.
(3) si la condición es falsa
K3 - J3
Si la condición es verdadera, Excel usa esta ecuación.
Resumen
Utilice la declaración SI proporcionada en lugar de la ecuación que estaba usando para calcular las horas de cada día.
Respuesta4
Tuve un problema similar al calcular horas que ocasionalmente pasaban de la medianoche sin información de día o fecha. Mis horas de inicio eran por la noche y a veces se extendían hasta el día siguiente, así que usé un par de fórmulas juntas para que me saliera bien:
=IF(D4< C4,TEXT(D4-C4+24,"h:mm"),TEXT(D4-C4,"h:mm"))
(tenga en cuenta que me vi obligado a agregar un espacio después del símbolo menor que por alguna razón de formato; no estoy usando un espacio allí en mi fórmula)
Es un simple si, donde en mi caso D4 es la hora de finalización y C4 es la hora de inicio. Si la hora de finalización es menor que la hora de inicio, debe haber terminado después de la medianoche. La declaración del medio da el tiempo total de trabajo corrigiendo después de la medianoche (+24). El final muestra el tiempo de trabajo si todo lo que necesita es una resta regular. Descubrí que sin el "TEXTO" y el formato de hora, las matemáticas +24 se mostrarían como un valor decimal.