☰

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

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Determinar se o tempo especificado está dentro de 15 minutos do tempo alvo no Excel

Estou tentando criar uma planilha para monitorar a pontualidade dos backups executados. Os backups devem ser executados dentro de 15 minutos do horário alvo, para serem considerados em conformidade. Os dados brutos baixados do aplicativo de monitoramento on-line possuem as seguintes colunas relevantes:

  • Data da transação
  • Etiqueta de fita
  • Horário agendado
  • Hora de execução

Eu adicionei as seguintes colunas adicionais a ele:

  • Compatível (Sim/Não)

    =SE(E(TEMPO(HORA(D3), MINUTO(D3), SEGUNDO(D3))>F3,TEMPO(HORA(D3), MINUTO(D3), SEGUNDO(D3))<G3),"Sim", "Não")

  • -15min(Tempo agendado menos 15 minutos)

    =SE(OU(C3=TEMPO(0,0,0),C3=TEMPO(0,1,0)),TEMPO(23,59,0),C3)-TEMPO(0,15,0)+SE (C3=TEMPO(0,0,0),TEMPO(0,1,0),TEMPO(0,0,0))+SE(C3=TEMPO(0,1,0),TEMPO(0,2, 0),TEMPO(0,0,0))

  • +15min(Tempo agendado mais 15 minutos)

    =C3+"0:15"

Todas as colunas de hora usam o formato de hora “13:30”.

Quase funciona, mas há um problema quando o Horário Executado é “0,01” e o Horário Agendado é “0:00”. Isso é marcado como não compatível quando é compatível. Por favor, alguém pode ajudar a fazer este trabalho?

Exemplo de arquivo

Estou usando o MS Office 2013.

ATUALIZAR

Outros critérios:

  • O “Horário Agendado” é o horário em que o backup é executado em qualquer dia em que for executado, portanto não pode incluir uma data, pois é válido para todas as datas.
  • Às vezes, os backups são executados antes do horário programado. Tudo bem, desde que seja dentro de 15 minutos do horário programado.

Percebi que preciso alterar um pouco minha planilha para ficar assim, com as datas do mês estendendo-se para a direita, conforme abaixo, e removerei também a data da transação, pois a transação aparecerá na data relevante. No entanto, use o acima para fins de exemplo (a imagem abaixo é fornecida para ajudar a entender o que precisamos acomodar).

insira a descrição da imagem aqui

Responder1

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

OR existe para testar se a hora real se refere ao dia anterior ou posterior. É necessário funcionar para esses casos de margem.

Observe que isso C3+1significa qualquer hora em C3 + 1 dia. Isso funciona porque o Excel armazena data e hora da mesma maneira, como um número de dias a partir de 01/01/1900. Os decimais são, na verdade, tempo armazenado como uma fração de um dia. Se você inserir algum horário, digamos 12h, ele será armazenado como um número 0,5. A mesma hora em 02/01/1900 seria 2,5.

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

Responder2

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 aquele 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 de início é menor que o horário programado menos 15 minutos (portanto, no dia seguinte e se for mais de 15 minutos antes, não é compatível de qualquer maneira) e adiciona um dia ao carimbo de data e hora.

Então 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: Lendo todos os seus comentários anteriormente, a hora de início e a hora programada são objetos de data e hora, então algo simples como isso deve funcionar.

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

Responder3

Estou pensando que isso não pode ser feito com a forma como você o configurou atualmente. Apenas usando as funções TIME, o Excel não consegue diferenciar entre ontem e hoje. Em segundo plano, TIME vai de 0 à meia-noite até 0,99999999 às 23h59:59. Ele não passa para 1,0000, 1,0001, etc., até onde sei.

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

Responder4

você deve considerar a coluna de data.

A: Data da transação
C: Cronograma
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))

informação relacionada