Fórmula do Excel para interpretar 4.15 como 4 horas, 15 minutos

Question

Estou criando um quadro de horários / rota para minha empresa e cada célula contém o horário IN e o horário OUT, apresentados como "9-3".

Eu criei uma função que irá somar a string de 6 dias, calculando o quanto diferença numérica existe entre 9 e 3, com o 3 sendo calculado como 15 dentro da fórmula. Isso funciona bem como um tempo fixo, mas a interpretação do tempo é interrompida quando se inclui algo diferente de um número inteiro. "9.30-3" retorna um número que não interpreta como a hora correta. Isso é compreensível, já que não estou registrando os números como o tempo, mas isso é predominantemente devido ao formato dos dados de origem - ou seja, o intervalo com hífen.

Eu entendo que o cálculo do tempo é resolvido tendo o timing decimal correto e reformatando a célula para exibir o tempo interpretado, entretanto a natureza dos meus dados de origem não permitirá isso, ou pelo menos eu tenho que alterar um já enorme equação para quantificar corretamente o tempo a partir do decimal.

Além disso, é importante saber que as informações inseridas estarão no formato 4.15 para as 16h15, e não em 15 / 100ths da hora, pois o Excel as lerá como.

    

Answer 1

Ok, então eu mesmo consegui fazer isso. Percebi que o formato de hora não seria muito facilmente extraído da célula, então decidi analisar os elementos da célula. Isso significa que, a partir de uma célula que leia "9.30-5.15" no formato de texto, é necessário que o Excel seja compreendido:

• Comece o turno às 9 horas
• & 30 minutos
• & 0 segundos
• Deixar turno às 5 horas
• & 15 minutos
• & 0 segundos
• 5 horas é menos de 9 horas, por isso adicione 12 horas para contabilizar 24 horas
• Deduza o tempo agora maior a partir do agora menos tempo

A fórmula a seguir é bem feia de se ver, mas certamente funciona (até certo ponto).

=IF(ISBLANK(A1),TIME(0,0,0),IFERROR(IF((--MID(A1,FIND("-",A1)+1,9999))<=(--LEFT(A1,FIND("-",A1)-1)),TIME(IFERROR(MID(A1,FIND("-",A1)+1,LEN(A1)-SEARCH(".",A1,SEARCH("-",A1))-1),RIGHT(A1,LEN(A1)-FIND("-",A1))),IFERROR(RIGHT(A1,LEN(A1)-SEARCH(".",A1,FIND("-",A1))),0),0)-TIME(IFERROR(MID(A1,1,SEARCH(".*-",A1)-1),(LEFT(A1,FIND("-",A1)-1))),IFERROR(MID(A1,SEARCH(".*-",A1)+1,SEARCH("-",A1)-SEARCH(".*-",A1)-1),0),0)+TIME(12,0,0),TIME(IFERROR(MID(A1,FIND("-",A1)+1,LEN(A1)-SEARCH(".",A1,SEARCH("-",A1))-1),RIGHT(A1,LEN(A1)-FIND("-",A1))),IFERROR(RIGHT(A1,LEN(A1)-SEARCH(".",A1,FIND("-",A1))),0),0)-TIME(IFERROR(LEFT(A1,FIND(".*-",A1)-1),(LEFT(A1,FIND("-",A1)-1))),IFERROR(MID(A1,FIND(".",A1)+1,SEARCH(".*-",A1)),0),0)),(TIME(0,0,0))))

Esta fórmula permite que uma célula em branco seja contada como o tempo zero, também para uma célula preenchida com palavras a serem contadas como zero, para não estragar o cálculo.

O propósito disto é que uma rota mensal seja criada e a hora seja inserida nos seguintes formatos:

• 9-3
• 9,30-3
• 9-3.30
• 9,30-3,30

Isso significa que o gerente de turno pode inserir horas (que podem mudar com a variação diária) e a planilha calculará 6 dias úteis da semana, destacando o cálculo ao atingir sua cota de horas por meio de formatação condicional, como na imagem: Cálculo da rota do quadro de horários

Espero que isso ajude alguém tentando fazer o mesmo !! Há apenas um bug com o qual não lidei - você não pode digitar uma hora de 2 dígitos como a segunda vez. Então você pode entrar 3-9.59 mas você não pode entrar 3-10. Isto é simplesmente porque ninguém onde eu estou projetando isso para as obras após as 6 da tarde, e eu não precisava para corrigi-lo .. então por que se preocupar. Não é muito difícil de corrigir, no entanto, se você quiser usá-lo.

Por favor, deixe-me saber se isso é útil para você, já que estou mexendo meu cérebro para fazer esse trabalho do jeito que faz há algum tempo!

    

Answer 2

Digamos que temos em A1 :

8,45-2,23

Em B1 digite:

=--LEFT(A1,FIND("-",A1)-1)

e em C1 digite:

=--MID(A1,FIND("-",A1)+1,9999)

Essas duas células são versões "decimais" dos horários de início e término. Em D1 ajustamos para AM / PM:

=IF(C1<B1,C1+12,C1)

Finalmente, em E1 , digite:

=TIMEVALUE(SUBSTITUTE(D1,".",":"))-TIMEVALUE(SUBSTITUTE(B1,".",":"))

Comformataçãorazoávelaplicada.

Estespodemsercombinadosemumaúnicafórmula,sedesejado:

=TIMEVALUE(SUBSTITUTE((IF((--MID(A1,FIND("-",A1)+1,9999))<(--LEFT(A1,FIND("-",A1)-1)),(--MID(A1,FIND("-",A1)+1,9999))+12,(--MID(A1,FIND("-",A1)+1,9999)))),".",":"))-TIMEVALUE(SUBSTITUTE((--LEFT(A1,FIND("-",A1)-1)),".",":"))