Excel Como contar as horas uteis de um carro parado na oficina

[Pedrorocha93]

Bom dia, como sou leigo no assunto, não sei bem desenvolver o que preciso. Estou desenvolvendo uma planilha em que consta data e hora de entrada e saida de um caminhão na oficina mecânica. Por meio disto preciso calcular a quantidade de horas que ele ficou parado para chegar ao resultado de disponibilidade semanal deste equipamento.

A carga horária da empresa é de 7h as 17h, porém há 1 hora de almoço, o que nos trás a 9 horas úteis por dia, sendo que as sextas feiras o horário de expediente é de 7:00 as 16:00, com 1 hora de almoço também (porém caso exista uma complexidade por parte disso, a fórmula pode ser baseada como se na sexta fosse um dia normal).

 

Nas minhas pesquisas pela internet encontrei está fórmula que não me atende 100% devido ela ser destinada a uma jornada de 10h por dia.

 

=(DIATRABALHOTOTAL(G8;H8;FERIADOS!A3:A14)-2)*10/24+MÁXIMO(0;17-MÁXIMO(MOD(G8;1)*24;7))/24+MÁXIMO(0;MÍNIMO(MOD(H8;1)*24;17)-7)/24

 

Para simplificar, estou anexando a planilha em anexo com esta fórmula aplicada, conto com a colaboração de todos.

Correção Controle de Manutenções e Disponibilidade de Frota.xlsx

[Visitante]

 

Experimente:

 

=((DIATRABALHOTOTAL(G8;H8;FERIADOS!$A$3:$A$14)-2)*9+SE(MOD(G8;1)*24<12;17-MOD(G8;1)*24-1;17-MOD(G8;1)*24)+SE(MOD(H8;1)*24<12;17-MOD(H8;1)*24-1;17-MOD(H8;1)*24)-DIATRABALHOTOTAL.INTL(G8;H8;"1111011";FERIADOS!$A$3:$A$14))/24

Se o resultado não for o esperado disponibilize nova amostra do seu arquivo com apenas a planilha FERIADOS e outra planilha com todas as variações de datas e horários de início e fim possíveis de ocorrer e com o resultado esperado para cada caso.

 

dicas:

1. não mescle células; células mescladas são desnecessárias/inúteis em 99,99% dos casos e ainda podem provocar erros em fórmulas e em macros

2. não deixe linhas vazias entre os dados da tabela, isso limita a aplicação de recursos//ferramentas do Excel

 

Se caso você precisar disponibilizar nova amostra do seu arquivo por gentileza observe as dicas acima.

 

obs. considerei o horário de trabalho das 7 às 12 e das 13 às 17h e às sextas-feiras até 16h

[Pedrorocha93]

Bom dia,

Fico muito grato por todo esforço e ajuda porém não consegui aplicar a fórmula a planilha.

 

"A fórmula digitada contém um erro"

 

Estou usando o Ms Excel 2007 na empresa que trabalho 

[Visitante]

 

Desmembrei em 4 partes a fórmula que passei antes, conforme abaixo. Por favor cole cada fórmula em uma célula vazia e informe qual delas apresenta erro.

 

=DIATRABALHOTOTAL(G8;H8;FERIADOS!$A$3:$A$14)-2
=SE(MOD(G8;1)*24<=12;17-MOD(G8;1)*24-1;17-MOD(G8;1)*24)
=SE(MOD(H8;1)*24<12;17-MOD(H8;1)*24-1;17-MOD(H8;1)*24)
=DIATRABALHOTOTAL.INTL(G8;H8;"1111011";FERIADOS!$A$3:$A$14)

E faça o teste com esta também.

=DIATRABALHOTOTAL.INTL(G8;H8;1;FERIADOS!$A$3:$A$14)

 

[Patropi]

Boa tarde @Pedrorocha93

 

Apliquei a formula que o Osvaldo postou na tua planilha e ela não apresentou erro.

 

Confira na planilha e dê retorno.

 

Não se esqueça de clicar em Curtir na resposta do Osvaldo e na minha.

 

[]s

 

 

06_09_18_Correção Controle de Manutenções e Disponibilidade de Frota.xlsx

[Pedrorocha93]

Pois bem, segui com os testes aqui, eu colo a fórmula e ela não interage com a planilha, fica apenas escrita. Assim que eu clico no corpo dela e clico enter novamente ela informa que "a fórmula digitada contém um erro". Acusou com todas as 4 partes que foram desmembradas. Será que há algum problema pelo fato do Excel ser 2007?

adicionado 8 minutos depois

@Patropi fiz download da sua planilha, ela vem com um número, mas assim que clico em enter na fórmula ou escrevo qualquer valor no campo das datas me apresenta o erro #NOME?. Fui até "mostrar etapas do calculo" e me parece que o erro está vindo de "NETWORKDAYS.INTL". Na fórmula do @osvaldomp ela vem escrita como "DIATRABALHOTOTAL.INTL" Mas o Excel aparentemente não consegue ler essa fórmula. Alguma sugestão? 

[Visitante]

 

1 hora atrás, Pedrorocha93 disse:

... eu colo a fórmula e ela não interage com a planilha, fica apenas escrita.

Talvez a célula esteja formatada para Texto

Assim que eu clico no corpo dela e clico enter novamente ela informa que "a fórmula digitada contém um erro".

Você testou em outras máquinas?

Acusou com todas as 4 partes que foram desmembradas. Será que há algum problema pelo fato do Excel ser 2007?

A função DIATRABALHOTOTAL.INTL está disponível somente a partir da versão 2010 do Excel, porém as demais deveriam funcionar no XL 2007.

adicionado 8 minutos depois

@Patropi...o erro está vindo de "NETWORKDAYS.INTL".

@osvaldomp Estranho pois essa sintaxe é da versão em Inglês.

 

 

Reiterando abaixo o que comentei antes.

Em 05/09/2018 às 14:37, osvaldomp disse:

Se o resultado não for o esperado disponibilize nova amostra do seu arquivo com apenas a planilha FERIADOS e outra planilha com todas as variações de datas e horários de início e fim possíveis de ocorrer e com o resultado esperado para cada caso.

 

dicas:

1. não mescle células; células mescladas são desnecessárias/inúteis em 99,99% dos casos e ainda podem provocar erros em fórmulas e em macros

2. não deixe linhas vazias entre os dados da tabela, isso limita a aplicação de recursos//ferramentas do Excel

 

Se caso você precisar disponibilizar nova amostra do seu arquivo por gentileza observe as dicas acima.

 

obs. considerei o horário de trabalho das 7 às 12 e das 13 às 17h e às sextas-feiras até 16h

 

[Pedrorocha93]

11 horas atrás, osvaldomp disse:

 

 

 

Reiterando abaixo o que comentei antes.

 

 

As células não estão como texto.

 

Existe alguma forma de chegar a algum resultado sem a fórmula "DIATRABALHOTOTAL.INTL"?

 

Pois como sou empregado da empresa fico limitado ao Excel 2007 utilizado por eles. 

 

Amanhã farei mais algumas tentativas.

 

Continuo agradecendo toda atenção que vem dando a minhas dúvidas.

[deciog]

@Pedrorocha93 , Bom Dia.

 

Você pode anexar o modelo com preenchimento dos campos para que possamos fazer a formula correta, fica mais fácil

 

Decio

 

[Pedrorocha93]

4 horas atrás, deciog disse:

@Pedrorocha93 , Bom Dia.

 

Você pode anexar o modelo com preenchimento dos campos para que possamos fazer a formula correta, fica mais fácil

 

Decio

 

Segue em anexo a planilha com algumas informações referentes a primeira semana.

Correção Controle de Manutenções e Disponibilidade de Frota.xlsx

[deciog]

@Pedrorocha93 , Bom Dia.

 

Você mandou o mesmo anexo anterior, sem preenchimentos das data e horas

 

Decio

 

[Pedrorocha93]

@deciog realmente errei ao anexar. Segue em anexo o documento correto.

1536680650752_Correção Controle de Manutenções e Disponibilidade de Frota.xlsx

[deciog]

@Pedrorocha93

 

Segue confere se desta forma atende seu projeto, caso tenha erro poste novamente com o resultado esperado

 

Se foi útil clica no curtir é uma forma de agradecimento pela ajuda

 

Decio

 

1536680650752_Correção Controle de Manutenções e Disponibilidade de Frota Decio.xlsx

[Pedrorocha93]

@deciog infelizmente os resultados não estão batendo com o esperado. Em determinados momentos o cálculo excede o esperado. Talvez eu não tenha sido claro. Precisava de uma fórmula para descontar 1 hora por dia do calculo total, independente da hora de entrada e saída do caminhão. E na sexta feira esse decréscimo seria de 2 horas, para atingir o total de 44 horas semanais. Por exemplo, se um caminhão deu entrada no dia de hoje as 7:00AM e saiu as 10:00AM teoricamente a permanência dele foi de 3 horas certo? Mas como devo descontar 1 hora de almoço e não se pode estipular um horário fixo para o almoço(ou pode?), a fórmula deveria me retornar 2 horas de permanência. Não sei se minha visão está correta, me corrijam se estive errado.

[Pedrorocha93]

Tomei a liberdade de fazer uma alteração na fórmula e funcionou. No seguinte valor negritado.

 

=SE(G14="";"";(DIATRABALHOTOTAL(G14;H14;FERIADOS!$A$3:$A$14)-2)*SE(TEXTO(INT(G14);"ddd")="sex";8;9)/24+MÁXIMO(0;17-MÁXIMO(MOD(G14;1)*24;7))/24+MÁXIMO(0;MÍNIMO(MOD(H14;1)*24;17)-9)/24)

 

Porém a sexta feira não retorna o valor desejado. Precisava qu e neste dia fosse descontado 2 horas do tempo de permanência do veículo. Mesmo assim agradeço muito pela atenção dada.