Formatação condicional do Excel 2010 - TIME

Question

Eu preciso fazer uma planilha com formatação condicional. Eu não sei como fazer isso. O que estou tentando fazer é o Check in Times. Assim, Bob Will partirá de nossa loja às 8:00 da manhã e ele fará o check-in com despacho quando chegar na metade do caminho de chegada. Em seguida, ele fará o check-in novamente quando chegar ao local.

Preciso do Check-in até a metade e do Check-in na chegada para alterar as cores. Se Bob fez check-in 15 minutos mais cedo, seja na metade do caminho, ou na chegada, eu preciso que a célula vá em AMARELO .. Se o Bob fizer o check-in, preciso que a célula fique laranja e se o Bob checar 15 minutos atrasado, eu preciso daquela célula. ,

No entanto, o horário de partida e o horário de chegada serão diferentes em cada linha, porque nem todos saem ao mesmo tempo e nem todos estão indo para o mesmo local.

Praticamente eu preciso de um formato de hora para que quando eu disser que a partida é às 8:00 e que o horário de check-in seja 10:00, mas Bob chegou lá às 10:30 eu preciso que a célula fique vermelha porque ele está atrasado ..

    

Answer 1

(Esta é a minha primeira resposta postada neste site, então conselhos sobre como melhorar minha resposta são bem-vindos)

Eu não sei como sua planilha está configurada, mas a maneira mais fácil de fazer isso funcionar é configurar colunas assim: Um: empregado B: Hora de Partida C: Tempo de Meia Espera Esperado D: Tempo Real a Meio Caminho E: hora prevista de chegada F: Hora de chegada real

Como você preenche esses campos depende de você No entanto, uma vez que eles estão, você pode usar a formatação condicional nas colunas D e F usando fórmulas como esta: (multiplicando por 1440 transforma um número de data / hora em minutos)

=((C2-D2)*1440)>15 (Yellow, Stop if True)

=((D2-C2)*1440)>15 (Red, Stop if True)

=D2>0 (Orange)

Esta configuração muda o tempo médio real amarelo se for 15 minutos antes do tempo esperado, vermelho se for 15 minutos depois, e laranja se for qualquer outro valor diferente de zero (ou seja, quando a hora é colocada e não 15 minutos mais cedo ou mais tarde desde que teria sido capturado pelas fórmulas anteriores)

Note que você terá que colocar em 3 condições diferentes de formato condicional e você precisa ter certeza de que a laranja está na parte inferior, então ela só é verificada depois que Vermelho e Amarelo são verificados primeiro.

    

Answer 2

Depois de analisar a versão original da minha resposta, percebi que a consolidação de todas as fórmulas de formatação necessárias era realmente um pouco mais fácil do que eu esperava. Isso e eu estava usando as cores erradas. Veja como você pode fazer tudo que precisa, totalmente na formatação condicional.

Todos os campos abaixo são inseridos manualmente. Certifique-se de que as colunas C, E e F estejam formatadas em um formato de data / hora.

Aquiestáumacapturadeteladasregrasdeformataçãocondicional.Nãoénecessáriousar"Stop If True" aqui, pois as fórmulas de "early" e "late" não se sobrepõem. A ordem de prioridade das regras "early" e "late" não importa, mas você precisa ter certeza de que "on time" é a última processada.

Fórmulas:

EarlyHalfway(AplicaràcolunaE):

=E1<=C1+TIME(0,D1/2-15,0)

LateHalfway(AplicaràcolunaE):

=E1>=C1+TIME(0,D1/2+15,0)

Chegadaantecipada(aplicaràcolunaF):

=F1<=C1+TIME(0,D1-15,0)

Chegadatardia(aplicaràcolunaF):

=F1>=C1+TIME(0,D1+15,0)

OnTime(AplicaraE:F;certifique-sedequeéaúltimaregraprocessada.):

=AND(ISERROR(SEARCH("Actual",E1)),NOT(ISBLANK(E1)))

Efetivamente, a regra "Early Halfway" se traduz em:

Take the Travel Time, divide it by two, subtract fifteen minutes, and add it to the Departure Time. If the Actual Halfway Time is less than or equal to that value, highlight the cell.

Você provavelmente pode descobrir o resto das regras Early / Late a partir daí. Nota: As regras antecipadas dispararão um falso positivo em uma célula vazia se o horário de partida estiver preenchido na mesma linha. Dada a regra "On Time" que usei, (veja abaixo) provavelmente você pode descobrir como evitar isso se quiser.

A regra "On Time" é basicamente um padrão que serve para acionar qualquer coisa, exceto os cabeçalhos ou uma célula vazia. Isso mantém sua planilha com aparência simples, ao mesmo tempo em que é relativamente simples (leia-se: não é maior que / menos que ou matemática) "qualquer coisa que não corresponda aos outros". Efetivamente, isso se traduz em:

If a search for the string "Actual" results in an error (i.e.: "Actual" does not exist in the cell), AND the cell is NOT blank, highlight the cell.

Claro que, como já foi dito, a regra "On Time" só funciona corretamente se for a última regra processada.