Questão Determinar se o horário especificado está dentro de 15 minutos do tempo de destino no Excel


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 possuem as seguintes colunas relevantes:

  • Data da Transação
  • Etiqueta de fita
  • Hora agendada
  • Tempo executado

Eu adicionei as seguintes colunas adicionais a ele:

  • Complacente (sim / não) 

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

  • -15 min (Hora programada menos 15 minutos)

    = SE (OR (C3 = HORA (0,0,0), C3 = HORA (0,1,0)), HORA (23,59,0), C3) -TIME (0,15,0) + SE (C3 = HORA (0,0,0), HORA (0,1,0), HORA (0,0,0)) + SE (C3 = HORA (0,1,0), TEMPO (0,2, 0), TIME (0,0,0))

  • + 15min (Hora agendada 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 esse trabalho?

Example of File

Estou usando o MS Office 2013.

ATUALIZAR

Outros critérios:

  • O "Horário de agendamento" é o horário em que o backup é executado em qualquer dia em que é executado, portanto, não pode 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).

enter image description here


3


origem




Respostas:


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 antes ou depois. É necessário trabalhar para esses casos de margem.

Observe que C3+1 significa qualquer tempo 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 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.

Pode dar-lhe falsos positivos, no entanto, se o seu backup real estiver atrasado ou adiantado por um período entre as 23h45 e as 24h15m.


1



Eu substituí sua fórmula, mas infelizmente, o comportamento dessa linha é exatamente o mesmo que o meu e é isso que estou tentando superar. Este backup foi compatível, mas foi marcado como não compatível. Boa tentativa embora. Obrigado. - Peter Snow
Isso é estranho. Para mim, funciona perfeitamente (Excel2010, no entanto - imgur.com/Y8DI1AM). Como o seu tempo é armazenado lá, como texto talvez? - JollyMort
O tempo é armazenado como formato "Time 13:30". Apenas tentei de novo. Mesmo resultado. Marcando a linha 3 como não compatível. - Peter Snow
Ah, eu vi em outros comentários que você tinha a data armazenada lá também. Agora, consertou isso tirando apenas o tempo do valor armazenado. Tente novamente por favor, deve funcionar agora. - JollyMort
Sim Siree! Isso funcionou. Obrigado pela ajuda :) - Peter Snow


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 é o horário de início (coluna F).

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

Essa 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 se adiciona um dia ao carimbo de data / hora.

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

Edit: Lendo todos os seus comentários mais cedo a hora de início e hora programada são objetos de data e hora para que algo simples como este funcione.

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

1



Estava parecendo bom senhor, até que eu li que você está supondo que os backups nunca começam antes do horário agendado. Infelizmente, estou tendo que acomodar isso também, olhando para os dados brutos baixados. - Peter Snow
Senhor, por favor, você pode alinhar suas colunas com as da amostra? Ou, se não, por favor, diga-me a qual coluna "A2" está se referindo, pois precisarei atualizá-lo. O mesmo com as outras colunas mencionadas em suas fórmulas. - Peter Snow
Não consigo tornar a coluna de horário de programação uma coluna de data / hora, pois a programação é toda vez que é executada. Na verdade, acabei de perceber que também precisarei remover a coluna Data da transação por causa disso, como muitos backups são diários e pretendo expandir a planilha para a direita, para acomodar os tempos reais de execução de cada dia em um mês. para cada dia eu teria 4 colunas (Start Time, Compliant, -15min e + 15min). - Peter Snow
Vou tentar chegar a uma solução mais fácil. Você pode colocar todas essas informações extras também na sua pergunta? - Michthan
Ok obrigado. Eu atualizei minha pergunta para torná-la mais clara. - Peter Snow


Eu 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.


0



Obrigado. O tempo integral é importado para o "Horário de Início". É "6/1/2016 12:01:00 AM", mas não é exibido porque o campo é formatado como um campo "Time 13:30", mas é exibido quando você clica nele. Talvez eu precise ter isso traduzido nos campos mais e menos também. Eu vou ver se consigo fazer isso. - Peter Snow


você tem que considerar a coluna de data.

A: data da transação
C: Agenda
D: hora de início
E: complacente

-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 compatível: (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))

0



Senhor, a coluna "B" é o rótulo da fita. É isso que você estava se referindo em "B6"? Além disso, ao inserir a fórmula "Compatível", recebo um erro. - Peter Snow
veja o código corrigido considerando a coluna Label da etiqueta como B. O resultado é booleano: 0 = False, 1 = True - visu-l
Oi senhor, eu tentei ambas as suas soluções. Em ambos os casos, o resultado é zero (não compatível), mas deve ser 1 para o exemplo mostrado no meu post acima (linha 3). - Peter Snow