Calculando a diferença entre 2 vezes, excluindo fins de semana, calculando a média dos resultados

Question

Eu tenho uma planilha do Excel 2007 onde eu preciso calcular a diferença entre 2 vezes, excluindo os finais de semana e, em seguida, no final de uma semana ou mês, ser capaz de encontrar a média dos valores de tempo na coluna 'diferença' .

Aqui está o que eu tenho até agora:

=NETWORKDAYS(A3,H3)-1&" DAYS, "&TEXT(H3-A3,"HH"" HRS AND ""MM""MIN"""))

O que me dá um resultado de 1 DAYS, 02 HRS E 03MIN , o que é bom; Eu não consigo descobrir uma maneira de encontrar uma média dos dados nessa coluna com o tempo expresso neste formato.

Alguma sugestão?

Muito obrigado.

    

Answer 1

Sua fórmula existente nem sempre funciona, por exemplo se A3 é uma quinta-feira às 10:00 e H3 é o dia seguinte às 09:00, o resultado não deve ser

0 DAYS, 23 HRS AND 00MIN

mas o resultado real com essa fórmula é 1 dia muito alto, ou seja

1 DAYS, 23 HRS AND 00MIN

Por que não retornar o resultado em hh: mm e, em seguida, você pode calcular a média, ou seja, usar essa fórmula

=NETWORKDAYS(A3,H3)-1+MOD(H3,1)-MOD(A3,1)

resultados de formato personalizado como [h]: mm, você pode simplesmente calcular a média da coluna de resultados

Nota: a fórmula funciona assumindo que A3 e / ou H3 são sempre dias da semana

Solução alternativa:

Use essa fórmula para corrigir o problema que identifiquei

=NETWORKDAYS(A3,H3)-1-(MOD(H3,1)< MOD(A3,1))&" DAYS, "&TEXT(H3-A3,"HH"" HRS AND ""MM"" MIN""")

Supondo que você tenha resultados usando essa fórmula em J3: J10, tente esta fórmula para obter uma média no mesmo formato

=SUMPRODUCT(MID(0&J3:J10,FIND({"D","H","M"},J3:J10)-2,3)/{1,24,1440})/ROWS(J3:J10)

formatar célula de resultados como personalizada:

d" DAYS, "hh" HRS AND "mm" MIN"

Nota: isto pressupõe que nenhum período seja superior a 99 dias úteis e em que os dias úteis médios são < 32 dias