Um dia era pra mostrar coisas sobre o excel, hoje é para compartilhar meus poucos conhecimentos, com os interessados!

terça-feira, 12 de junho de 2012

Excel - Monitoramento, Relatórios e Métricas de TI - Contar dias de SLA (Dias de 10h)



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 apartir 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(Y2="";"BACKLOG";
SE((((DIATRABALHOTOTAL(W2;SE(ÉERROS(PROCV(P2;Siebel!A:C;3;0));Y2;PROCV(P2;Siebel!A:C;3;0));Feriados!A$2:A$65536)-1)/24)*10)<0;0;(((DIATRABALHOTOTAL(W2;
SE(ÉERROS(PROCV(P2;Siebel!A:C;3;0));Y2;PROCV(P2;Siebel!A:C;3;0));Feriados!A$2:A$65536)-1)/24)*10))+SE(MOD(W2;1)<horaF;MOD(Y2-W2;1);
SE(W2>horaF;MOD(Y2-W2;1);SE(MOD(W2;1)>horaF;HORAZERO;MOD(horaF-MOD(W2;1);1))+
SE(horaF<MOD(SE(ÉERROS(PROCV(P2;Siebel!A:C;3;0));Y2;PROCV(P2;Siebel!A:C;3;0));1);HORAZERO;MOD(MOD(SE(ÉERROS(PROCV(P2;Siebel!A:C;3;0));Y2;PROCV(P2;Siebel!A:C;3;0));1)-horai;1)))))

Onde: 
W2 = DATA DE ABERTURA
Y2 = DATA DE ENCERRAMENTO

HORAI = HORA INICIAL DO DIA, (NESTE CASO 18: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 Y2 E W2, 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 P2 É O NUMERO DO CHAMADO DE REFERENCIA CRUZADA.
MOD = QUE EXTRAI A HORA DE UMA CÉLULA.

O Calculo base é o DIATRABALHOTOTAL(W2;Y2;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 util só começar às 08:00, e faz o mesmo para encerramento fora deste ciclo.