☰

Determinar si el tiempo especificado está dentro de los 15 minutos del tiempo objetivo en Excel

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Determinar si el tiempo especificado está dentro de los 15 minutos del tiempo objetivo en Excel

Estoy intentando crear una hoja de cálculo para monitorear la puntualidad de las copias de seguridad ejecutadas. Las copias de seguridad deben ejecutarse dentro de los 15 minutos posteriores al tiempo objetivo para que se consideren conformes. Los datos sin procesar descargados de la aplicación de monitoreo en línea tienen las siguientes columnas relevantes:

  • Fecha de transacción
  • Etiqueta de cinta
  • Hora programada
  • Hora de ejecución

Le agregué las siguientes columnas adicionales:

  • Cumple (Sí/No)

    =SI(Y(TIEMPO(HORA(D3), MINUTO(D3), SEGUNDO(D3))>F3,TIEMPO(HORA(D3), MINUTO(D3), SEGUNDO(D3))<G3),"Sí", "No")

  • -15 minutos(Tiempo Programado menos 15 minutos)

    =SI(O(C3=TIEMPO(0,0,0),C3=TIEMPO(0,1,0)),TIEMPO(23,59,0),C3)-TIEMPO(0,15,0)+SI (C3=TIEMPO(0,0,0),TIEMPO(0,1,0),TIEMPO(0,0,0))+SI(C3=TIEMPO(0,1,0),TIEMPO(0,2, 0),TIEMPO(0,0,0))

  • +15min(Tiempo Programado más 15 minutos)

    =C3+"0:15"

Todas las columnas de hora utilizan el formato de hora “13:30”.

Casi funciona, pero hay un problema cuando el Tiempo Ejecutado es “0.01” y el Tiempo Programado es “0:00”. Esto se marca como no conforme, cuando sí lo es. ¿Alguien puede ayudarme a hacer que esto funcione?

Ejemplo de archivo

Estoy usando MS Office 2013.

ACTUALIZAR

Otros criterios:

  • La "hora programada" es la hora a la que se ejecuta la copia de seguridad en cualquier día en que se ejecute, por lo que no puede incluir una fecha, ya que es válida para todas las fechas.
  • En ocasiones, las copias de seguridad se ejecutan antes de la hora programada. Esto está bien, siempre y cuando sea dentro de los 15 minutos de la hora programada.

Me di cuenta de que necesito cambiar ligeramente mi hoja para que se vea como la siguiente con las fechas del mes extendiéndose hacia la derecha como se muestra a continuación y también eliminaré la fecha de la transacción, ya que la transacción aparecerá debajo de la fecha relevante. Sin embargo, utilice lo anterior para los ejemplos (la imagen a continuación se proporciona para ayudar a comprender lo que debemos adaptar).

ingrese la descripción de la imagen aquí

Respuesta1

Puedes hacerlo así:

=IF(OR(ABS(D3-INT(D3)-(C3-INT(C3)))<TIME(0,15,0),ABS(D3-INT(D3)-(C3-INT(C3))-1)<TIME(0,15,0),ABS(D3-INT(D3)-(C3-INT(C3))+1)<TIME(0,15,0)),"Yes","No")

O está ahí para probar si la hora real se refiere al día anterior o posterior. Es necesario que funcione para estos casos marginales.

Tenga en cuenta que eso C3+1significa cualquier tiempo en C3 + 1 día. Esto funciona porque Excel almacena la fecha y la hora de la misma manera, como una cantidad de días desde el 1/1/1900. Los decimales en realidad son tiempo almacenado como una fracción de un día. Si ingresa una hora, digamos 12:00, se almacenará como un número 0,5. A la misma hora el 2/1/1900 sería 2,5.

Sin embargo, podría generar falsos positivos si su copia de seguridad real llega tarde o temprano durante un período de entre 23:45 y 24:15.

Respuesta2

En mi ejemplo, las columnas A a D contienen los mismos datos que en su ejemplo. Luego, las columnas E, F y G se completan con las fórmulas que se encuentran debajo.

Puede agregar dos columnas con una marca de fecha/hora. Uno para el horario que es en su caso (columna E):

=A2+C2

Y uno que es la hora de inicio (columna F).

=IF(D2<C2-TIME(0,15,0),A2+1+D2,A2+D2)

Esta fórmula verifica si la hora de inicio es menor que la hora programada menos 15 minutos (por lo tanto, el día siguiente y si es más de 15 minutos antes, no cumple de ninguna manera) y luego agrega un día a la marca de fecha y hora.

Luego puede verificar si cumple con esta fórmula (columna G):

IF(AND(F2<E2+TIME(0,15,0),F2>=E2),"Compliant","Not compliant")

Editar: leer todos sus comentarios antes, la hora de inicio y la hora programada son objetos de fecha y hora, por lo que algo simple como esto debería funcionar.

=IF(AND(D2>C2-TIME(0,15,0),D2<C2+TIME(0,15,0)),"Compliant","Non compliant")

Respuesta3

Estoy pensando que no se puede hacer con la configuración actual. Con solo usar las funciones de TIEMPO, Excel no puede diferenciar entre ayer y hoy. En el fondo, el TIEMPO va de 0 a medianoche a 0,99999999 a las 23:59:59. Hasta donde yo sé, no se transfiere a 1.0000, 1.0001, etc.

Si puede incluir la marca de tiempo completa en su informe, entonces su fórmula se puede ajustar para darse cuenta de que 15 minutos antes podrían ser parte de ayer, o 15 minutos después podrían ser parte de una fecha futura.

Respuesta4

Tienes que considerar la columna de fecha.

A: Fecha de la transacción
C: Horario
D: Hora de inicio
E: Cumple

-15 Min column (F):  =A6+C6-TIME(0,15,0)  
+15 Min column (G):  =A6+C6+TIME(0,15,0)

Compliant column (E):   =--AND(A6+D6>=F6,A6+D6<=G6)

Resultado de conforme: (0 = No, 1 = Sí)

También puedes hacerlo sin la columna -/+ 15 Min:

=--AND(A6+D6>=A6+C6-TIME(0,15,0),A6+D6<=A6+C6+TIME(0,15,0))

información relacionada