Encontrando um erro na formatação com base na hora no Excel

Question

Eu tenho uma coluna de vezes que abrange um intervalo de vinte e quatro horas (não necessariamente começando à meia-noite ou ao meio-dia). O primeiro é preenchido manualmente e depois cada um é preenchido automaticamente de acordo com a fórmula " [prevcell]+TIME(0,30,0) ", onde a célula anterior é a célula imediatamente acima da atual. A primeira célula sempre estará na marca da hora, então será da forma "n: 00: 00" para algum inteiro n. Assim, todos os tempos subseqüentes estarão na nova marca de hora ou na marca de trinta minutos, significando que eles serão da forma "n: m0: 00" onde n é um inteiro e m é 0 xor 3.

Depois tentei usar o formato condicional das células para que qualquer célula que estivesse em uma marca de trinta minutos (idest: é da forma "n: 30: 00" para o inteiro n) tenha uma cor de fundo cinza claro ( enquanto todas as outras células são brancas / sem cor. Então, por exemplo, uma célula com 10:00:00 será branca, mas uma célula com 10:30:00 será cinza claro.

Eu fiz isso:

1. Tipo de regra: "Formatar apenas as células que contêm".

2. Formate as células com: "valor da célula", "igual a", " =TIME(HOUR([curcell]), 30, 0) ". (Onde a célula atual é a própria célula que está sendo formatada).

3. Formatação de cor de fundo apropriada selecionada.

A formatação funcionou para a maioria das células. Eu talvez entenda se não funcionou para a primeira ou última célula por algum motivo, mas normalmente. Para o que não funciona são exatamente as células com os seguintes valores: 11:30:00, 18:30:00 (6:30:00 pm), 21:30:00 (9:30:00 pm), 22 : 30: 00 (10: 30: 00h); estas células não são coloridas (brancas) quando deveriam ser coloridas. Funciona para todas as outras células, incluindo 23:30:00 (11:30:00), mesmo que não funcionou para o celular para 11:30:00 (a.m.). Não há células coloridas quando não deveriam; as únicas células que não são coloridas quando deveriam ser são as mencionadas anteriormente.

Quando eu altero o valor de semente / tempo na minha célula mais alta, surgem diferentes erros exatamente do mesmo tipo (geralmente, mas nem sempre, para os mesmos valores e, às vezes, para outros adicionais).

Alguém sabe o que está errado ou como corrigir isso?

Edite! Aqui estão algumas observações que eu tive:

• Não funciona por mais tempo após a meia-noite seguinte, independentemente do horário da minha primeira célula (exceto 00:00:00). A dependência do valor da primeira célula parece ser assim:
• 11:30 (a.m.) funciona por alguns momentos na minha primeira célula.
  1. Não funciona para 00:00:00, 00:30:00, 1:00:00, 1:30:00 e 2:00:00.
  2. Funciona às 2:30, 3:00, 3:30.
  3. Não funciona às 4:00, 4:30, 5:00, 5:30, 6:00, 6:30.
  4. Funciona para todos os horários após as 7:00, incrementando em 30 minutos, até e incluindo 11:30. (A consideração não se aplica mais após esse valor de semente).
• Eu não verifiquei extensivamente, mas de uma olhada rápida: 18:30:00, 21:30:00 e 22:30:00 nunca funcionam.
• Dependendo do valor da minha primeira célula, alguns outros n: 30: 00 vezes não funcionam (para o inteiro n), mas não consigo descobrir um padrão. Não há erros, tanto quanto eu posso dizer, para qualquer n: 00: 00 vezes (para o inteiro n).

Atualização # 1

Alterei a condição de formatação para: Formatar células com "valor da célula", "diferente de", " =TIME(HOUR([curcell]), 0, 0) ". (Onde a célula atual é a própria célula que está sendo formatada).

Isso parece ter melhorado a situação, mas não perfeitamente. Ele formata tudo corretamente, exceto pelo fato de que impropriamente cores células com o tempo: 20:30:00 (8:00:00 PM), 23:00:00 (11:30 PM), e (se estiver presente) todos os horários depois e incluindo a próxima meia-noite (24:00:00; aka: as próximas 12:00).

( Editar : Os tempos para os quais as cores impropriamente parecem depender do valor da minha primeira célula e talvez de outros fatores. Mas há sempre dois erros discretos no final do dia e problema da meia-noite.)

Atualização nº 2

O principal problema foi resolvido. No entanto, se alguém pode me dizer por que surgiu em primeiro lugar, eu ficaria muito grato. Mentes curiosas querem saber!

    

Answer 1

Verifique a captura de tela abaixo, mostrando com êxito o Formato de cor da parte de MINUTE em Gray Color in the Cells, se forem 30, como você pediu.

Siga estes passos.

1. Selecione o intervalo de dados onde você deve inserir o TIME.
2. Aplique o Formato da Célula como HH: MM: SS e insira os valores de TEMPO.
3. Selecione o DATA RANGE para aplicar o formato condicional.
4. Na Home TAB, clique em Formatação condicional, Novas regras e, finalmente, em Usar fórmula para determinar qual célula deve ser formatada.
5. Escreva esta Fórmula =MINUTE(A2)=30 e selecione o acabamento Cor com OK, onde A2 faz parte do Intervalo de Dados que você selecionou atualmente.

NB: Lembre-se de um melhor entendimento apenas na coluna B Eu mencionei PERÍODOS DE TEMPO desde que você escreveu também a formatação condicional não está funcionando após MID NIGHT e outros.

Além disso, na Coluna C, encontrei MINUTES das datas no Col A apenas para mostrar e combinar os valores na célula adjacente para garantir que minha solução seja apenas formatação de célula em cinza Cor se o valor Minuto for somente 30.

Espero que o trabalho seja para você.

NB: Como alternativa, siga as etapas acima escritas e, no lugar da fórmula =MINUTE(A2)=30 , você também pode usar essa, =TIME(0,MINUTE(A2),0)=TIME(0,30,0) .

    

Answer 2

Eu assumo que esse problema é causado pela maneira como o Excel calcula, veja este link

link

Então, quando você adiciona horários, o resultado não é exatamente no minuto, mas ligeiramente acima ou abaixo, para que você não obtenha uma correspondência exata com a comparação

A utilização da função MINUTE funcionará quando o valor estiver ligeiramente acima, mas não abaixo. Você pode verificar minha teoria usando a função SECOND em seus dados para ver se você tem algum valor de 59

Para uma correção, uma vez que você está lidando com minutos inteiros, você pode arredondar para o minuto mais próximo e, em seguida, verificar o valor do minuto, por exemplo, por um valor de tempo em A2

=MINUTE(MROUND(A2,"0:01"))=30

Atualização:

Eu verifiquei isso colocando os valores de tempo em uma coluna e usei sua fórmula na próxima coluna, então onde meus valores de tempo começaram em A2 usei essa fórmula em B2 copiada para baixo

=A2=TIME(HOUR(A2),30,0)

Às 18:30, recebo FALSE e, quando verifico os valores, o valor em A2 é esse

0,770833333333334

mas o valor de TIME(HOUR(A2),30,0) é este:

0,77083333333333 3

Portanto, nenhuma correspondência a menos que você arredonde os dados antes da comparação, conforme sugerido