Determinar se o tempo especificado está dentro de 15 minutos do tempo de destino no Excel

Question

Estou tentando criar uma planilha para monitorar a pontualidade dos backups executados. Os backups devem ser executados dentro de 15 minutos do tempo de destino, para serem considerados compatíveis. Os dados brutos baixados do aplicativo de monitoramento on-line têm as seguintes colunas relevantes:

• Data da transação
• Rótulo de fita
• Tempo agendado
• Tempo executado

Eu adicionei as seguintes colunas adicionais a ele:

• Compatível (Sim / Não)

  =IF(AND(TIME(HOUR(D3), MINUTE(D3), SECOND(D3))>F3,TIME(HOUR(D3), MINUTE(D3), SECOND(D3))<G3),"Yes","No")

• -15min (hora programada menos 15 minutos)

  =IF(OR(C3=TIME(0,0,0),C3=TIME(0,1,0)),TIME(23,59,0),C3)-TIME(0,15,0)+IF(C3=TIME(0,0,0),TIME(0,1,0),TIME(0,0,0))+IF(C3=TIME(0,1,0),TIME(0,2,0),TIME(0,0,0))

• + 15min (hora programada mais 15 minutos)

  =C3+"0:15"

Todas as colunas de tempo estão usando o formato de hora "13:30".

Quase funciona, mas há um problema quando o Tempo executado é "0,01" e o tempo programado é "0:00". Isso é marcado como não compatível, quando é compatível. Por favor, alguém pode ajudar a fazer isso funcionar?

EstouusandooMSOffice2013.

UPDATE

Outroscritérios:

• O"Horário da programação" é o horário em que o backup é executado no dia em que é executado, por isso não é possível incluir uma data como é verdadeira para todas as datas.
• Às vezes, os backups são executados antes do horário agendado. Tudo bem, desde que isso esteja dentro de 15 minutos do horário agendado.

Percebi que preciso alterar levemente minha planilha para se parecer com o seguinte, com as datas do mês se estendendo à direita como abaixo e removerei a data da transação também, pois a transação aparecerá na data relevante . No entanto, por favor, use o acima para o propósito dos exemplos (a imagem abaixo é fornecida para ajudar a entender o que precisamos acomodar).

    

Answer 1

Você pode fazer assim:

=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")

OU está lá para testar se o tempo real se refere ao dia anterior ou posterior. É necessário trabalhar para esses casos marginais.

Note que C3+1 significa qualquer tempo em C3 + 1 dia. Isso funciona porque o Excel armazena data & hora da mesma maneira, como um número de dias a partir de 1/1/1900. Os decimais são, na verdade, armazenados como uma fração de um dia. Se você digitar algum tempo, digamos, 12:00, ele será armazenado como um número de 0,5. Mesmo tempo em 1/2/1900 seria 2,5.

No entanto, ele pode fornecer falsos positivos se o backup real estiver atrasado ou adiantado por um período entre 23h45 e 24h15m.

    

Answer 2

No meu exemplo, as colunas A a D contêm os mesmos dados do seu exemplo. As colunas E, F e G são então preenchidas com as fórmulas abaixo.

Você pode adicionar duas colunas com um carimbo de data / hora. Um para o horário agendado que está no seu caso (coluna E):

=A2+C2

E um que é a hora de início (coluna F).

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

Esta fórmula verifica se o horário no horário de início é menor que o horário programado menos 15 min (assim no dia seguinte e se for mais de 15 min antes não é compatível de nenhuma maneira) e que adiciona um dia à data carimbo.

Do que você pode apenas verificar se é compatível com esta fórmula (coluna G):

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

Editar: Ler todos os seus comentários antes da hora de início e da hora agendada são objetos de data e hora, para que algo simples como esse funcione.

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

    

Answer 3

Estou pensando que isso não pode ser feito com a configuração atual. Apenas usando as funções TIME, o Excel não consegue diferenciar entre ontem e hoje. No fundo, o TEMPO passa de 0 à meia-noite para .99999999 às 23:59:59. Ele não rola para 1.0000, 1.0001, etc. ao melhor de meu conhecimento.

Se você conseguir colocar o carimbo de tempo integral no relatório, sua fórmula poderá ser ajustada para perceber que 15 minutos antes podem realmente fazer parte de ontem, ou 15 minutos depois, em uma data futura.

    

Answer 4

você tem que considerar a coluna da data.

A: data da transação
C: Agendar
D: Hora de início
E: compatível

-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 para conformidade: (0 = Não, 1 = Sim)

você também pode fazer isso sem a coluna - / + 15 Min:

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