Excel - Monitoramento, Relatórios e Métricas de TI - Contar horas de SLA


Este Calculo trabalha com nível 1(ilha de atendimento) e nível 2 (direcionado para técnico).
conta apenas 10 horas uteis de cada dia.
Está rotina calcula a partir das 08:00 e se encerra-se as 18:00.
Neste caso, eu não poderia esquecer de remover as horas remanescentes e excedentes do primeiro e ultimo dia.

Desenvolvi esta pequena formula, usa ainda hoje por muitos cujo já transitei em empresas.
Para cada situação algo muda, mas a base é sempre a mesma.
Formula desenvolvida para controle de SLA para tempo de chamados abertos, direcionados e contidos em primeiro nível de atendimento.

Então tá, segue a formula para tempo de horas uteis.



=SE(C2="";"BACKLOG";SE((((DIATRABALHOTOTAL(B2;SE(ÉERROS(PROCV(A2;Backlog2!A:C;3;0));C2;PROCV(A2;Backlog2!A:C;3;0));Feriados!A$2:A$65536)-1)/24)*10)<0;0;(((DIATRABALHOTOTAL(B2;SE(ÉERROS(PROCV(A2;Backlog2!A:C;3;0));C2;PROCV(A2;Backlog2!A:C;3;0));Feriados!A$2:A$65536)-1)/24)*10))+SE(MOD(B2;1)<horaF;MOD(C2-B2;1);SE(B2>horaF;MOD(C2-B2;1);SE(MOD(B2;1)>horaF;HORAZERO;MOD(horaF-MOD(B2;1);1))+SE(horaF<MOD(SE(ÉERROS(PROCV(A2;Backlog2!A:C;3;0));C2;PROCV(A2;Backlog2!A:C;3;0));1);HORAZERO;MOD(MOD(SE(ÉERROS(PROCV(A2;Backlog2!A:C;3;0));C2;PROCV(A2;Backlog2!A:C;3;0));1)-horai;1)))))

Onde: 

B2 = DATA DE ABERTURA
C2 = DATA DE ENCERRAMENTO
BACKLOG2 = O SEGUNDO SISTEMA SISTEMA

HORAI = HORA INICIAL DO DIA, (NESTE CASO 8:00)
HORAF = HORA FINAL DO DIA, (NESTE CASO 18:00)
HORAZERO = RETORNA "00:00:00". OU OUTRO VALOR INSERIDO

COMANDOS UTILIZADOS

SE = PARA CONDIÇÃO DE VERDADEIRO OU FALSO.

DIATRABALHOTOTAL = PARA CALCULAR O TOTAL DE DIA ENTRE B2 E C2, DESCONTANDO AS DATAS DE FERIADOS DO ANO.
ÉERROS = QUE INFORMA VERDADEIRO QUANDO RETORNA ERRO.
PROCV = PARA LOCALIZAR UMA SEGUNDA DATA DE FECHAMENTO, MENOR QUE A Y2. NO MEU CASO UTILIZO 2 SISTEMA, O DO CLIENTE E O DO PARCEIRO, ONDE A2 É O NUMERO DO CHAMADO DE REFERENCIA CRUZADA.
MOD = QUE EXTRAI A HORA DE UMA CÉLULA.

O Calculo base é o DIATRABALHOTOTAL(B2;C2;Feriados!A$2:A$65536)-1)/24)*10), este calculo conta o total de dia entre as datas, descontando o feriado, retira um dia, pois como não estamos contando dia de 24 horas, o dia é quebrado ou seja começa às 08:00 e termina as 18:00, então já não existirá 00:00:00 até 07:59:59 e 18:00:01 até 23:59:59, além disso ainda temos que realizar a diferença entre a hora de abertura e a hora de encerramento.

No calculo, também está escrito a solução quando por exemplo a hora de abertura for maior que a hora de encerramento. Ex.: chamado aberto 19:00, ele soma para o dia seguinte como 00:00:00, isso devido o dia útil só começar às 08:00, e faz o mesmo para encerramento fora deste ciclo.


Segue o link de download para você ver na pratica via google drive
#Excel #Monitoramento #Relatorios #Metricas #TI #Contar #horas #SLA #qbexcel
Comentários