Calcular total de horas úteis entre duas datas e calcular o dia de entrega ( horas úteis) a partir de uma matriz de classificação de SLA

Question

Prezados,

Após procurar alguns tópicos relacionados ao tema e não encontrar algo , resolvi postar aqui um passo a passo que utilizei para construir uma formula que calculasse as horas utéis para atendimento de uma demanda (baseado em duas datas / horarios informados) e uma data/horario estimado como limite de atendimento , baseado em uma matriz SLA. Vamos à primeira solução:

Calcular total de horas úteis entre duas datas:

• Dados de set up:

• Fomulas:

Celula A3 = Input normal com o formato [h]:mm:ss

Celula B3 =  Input normal com o formato [h]:mm:ss

Intervalo C3:C4 = Input no formato data ( a lista pode ser maior de acordo com os dias não úteis que você considerar)

Celula A7  = B3-A3 com o formato [h]:mm:ss

Celula B7 = 24:00:00 com o formato [h]:mm:ss

Celula A11 = Preencher a data/ hora inicio (de acordo com a configuração de formatação de sua conveniencia)

Celula B11 = Preencher a data/ hora fim(de acordo com a configuração de formatação de sua conveniencia)  

Formula de calculo (neste exemplo , apresentada na célula C11)

=(NETWORKDAYS.INTL(A11,B11,,Listas!$C$3:$C$4)-2)*Listas!$A$7/Listas!$B$7+MAX(0,Listas!$B$3-MAX(MOD(A11,1)*Listas!$B$7,Listas!$A$3))/Listas!$B$7+MAX(0,MIN(MOD(B11,1)*Listas!$B$7,Listas!$B$3)-Listas!$A$3)/Listas!$B$7

Neste exemplo, a planilha era nomeada como "Listas"

Calcular a data final esperada de uma demanda baseada em uma data início e uma matriz SLA:

• Dados de set up:

• Fomulas:

Neste segundo caso eu preferi fazer por etapas para fins didadicos, mas você pode criar como uma única instrução em seus trabalhos, vamos lá:

Celula A3 = Input normal com o formato [h]:mm:ss

Celula B3 =  Input normal com o formato [h]:mm:ss

Intervalo C3:C4 = Input no formato data ( a lista pode ser maior de acordo com os dias não úteis que você considerar)

Celula A7  = B3-A3 com o formato [h]:mm:ss

Celula B7 = 24:00:00 com o formato [h]:mm:ss

Celula A11 = Preencher a data/ hora inicio (de acordo com a configuração de formatação de sua conveniência) 

Celula B11 = Preencher de acordo com a matriz D7:E11, especificamente com algum valor previsto na lista da coluna D. Se souber trabalhar com listas, fica mais facil o preenchimento.

1- calcular o total de horas restantes do dia:

=(MAX(0,'Listas (2)'!$B$3-MAX(MOD(A11,1))))

2 - calcular horas restantes SLA (usando o range D7:E11):

=(IF(B11='Listas (2)'!$D$8,E8-B13,IF(B11='Listas (2)'!$D$9,E9-B13,IF(B11='Listas (2)'!$D$10,E10-B13,IF(B11='Listas (2)'!$D$11,E11-B13,"")))))

3- calcular o numero de dias inteiros para atender

=(IF(B15<=0,0,ROUNDUP(B15/'Listas (2)'!$A$7,0)))

4-calcular o numero de horas para o ultimo dia:

=(((B15/'Listas (2)'!$A$7)-INT(B15/'Listas (2)'!$A$7))*A7)

5-calcular a data/ hora limite  de atendimento visando cumprir o SLA:

=WORKDAY(A11,B17,$C$3:$C$4)+D13+$A$3

Espero ter ajudado ,

Abs

Francisco Gomes

Answer 1

Boa noite!

Mais perguntas: como resolver "os problemas" das células B13 e principalmente da célula B15? Ou como é exibido na figura abaixo é a forma correta?

Markmzz

Answer 2

Boa tarde!

Uma pergunta: as fórmulas sugeridas na imagem abaixo são viáveis?

Markmzz

Answer 3

Ola ,

se entendi bem  sua pergunta , a resposta é sim . Vale falar que meu Excel esta no idioma ingles e , caso o excel esteja em outra lingua ( como o portugues ) vale encontrar a funcao correspondente no idioma configurado.