Microsoft Excel: formatação numérica condicional

Question

Há uma coluna com períodos de tempo no formato hh:mm:ss . Os segundos não são usados. O período de três horas e dois minutos seria 03:02:00 . Como formatar condicionalmente as células para obter os seguintes resultados?

00:00:00 → 0 minutes

00:01:00 → 1 minute

00:02:00 → 2 minutes

01:00:00 → 1 hour

02:00:00 → 2 hours

03:01:00 → 3 hours, 1 minute

01:03:00 → 1 hour, 3 minutes

Eu tentei sem sucesso sequências de formato personalizadas como:

[<60]hh:mm:ss "minutes";[<120]hh:mm:ss "minute";[<3600]hh:mm:ss "minutes";hh "hour(s)," mm "minute(s)"

    

Answer 1

Não conheço nenhum formato de data / hora "padrão" incorporado para formatá-lo dessa maneira, então, sim, sua tentativa em um formato personalizado parece adequada.

No entanto, o motivo pelo qual não está funcionando para você é que os valores de tempo não são expressos em segundos, como você supõe - eles são armazenados em um valor decimal, em que 1 é de 24 horas.

Então você tem que usar os valores decimais equivalentes aos seus pontos de quebra desejados. Infelizmente, parece que o Excel limita o número de pontos de interrupção a três, por isso não posso obter pontos de interrupção adicionais para "1 minuto" (singular), etc. Mesmo que houvesse mais pontos de interrupção, você nunca conseguiria "N horas" porque precisa de pontos de interrupção infinitos (não há nenhuma condição que detecte um componente de zero minutos).

Assim, o mais próximo que posso obter com um formato personalizado é:

[<0.000694]"0 minutes";[<0.041666][m] "minutes";h "hours", m "minutes"

O que dá o seguinte:

00:00:00    0 minutes
00:01:00    1 minutes
00:02:00    2 minutes
00:03:00    3 minutes
01:00:00    1 hours, 0 minutes
01:01:00    1 hours, 1 minutes
02:00:00    2 hours, 0 minutes
02:01:00    2 hours, 1 minutes
01:03:00    1 hours, 3 minutes
23:59:59    23 hours, 59 minutes

Eu provavelmente sugeriria escrever uma função VBA e formatar o campo na fórmula.

Mas também sugiro que você considere se o valor deve ser um tempo em primeiro lugar. Armazenar um período decorrido seria melhor em uma unidade específica (como minutos no seu caso). Você ainda não pode ter pontos de interrupção infinitos, mas evita um problema óbvio com o acima - ele passa depois de 23 horas, 59 minutos.

EDIT : Para me divertir eu fiz uma fórmula que produz os resultados desejados. Não é um formato como sua pergunta pede, mas porque isso não deu exatamente o resultado desejado, imaginei que isso poderia ser uma boa alternativa:

=if(hour(a1)=0,if(MINUTE(A1)=0,"0 minutes",if(minute(a1)=1,"1 minute",minute(a1) & " minutes")),if(hour(a1)=1,if(MINUTE(A1)=0,"1 hour",if(minute(a1)=1,"1 hour, 1 minute","1 hour, " & minute(a1) & " minutes")),if(MINUTE(A1)=0,hour(A1) & " hours",if(minute(a1)=1,hour(A1) & " hours, 1 minute",hour(A1) & " hours, " & minute(a1) & " minutes"))))

00:00:00    0 minutes
00:01:00    1 minute
00:02:00    2 minutes
00:03:00    3 minutes
01:00:00    1 hour
01:01:00    1 hour, 1 minute
02:00:00    2 hours
02:01:00    2 hours, 1 minute
01:03:00    1 hour, 3 minutes
23:59:59    23 hours, 59 minutes

    

Answer 2

Selecione a coluna Hora e simplesmente aplique este formato, h" hours and "m" minutes" . Sua coluna "Data" ficará assim.

    

Answer 3

Você pode tentar usar TEXT() em uma fórmula com muitos IF() 'aninhados'. Isso ficaria muito complicado no seu caso, porque você tem tantos formatos possíveis. Eu contei 8 possibilidades diferentes.

Como exemplo, esta fórmula

=IF(HOUR(A1)=1,IF(MINUTE(A1)=1,TEXT(A1,"h"" hour, ""m ""minute"""),TEXT(A1,"h"" hour, ""m ""minutes""")),TEXT(A1,"h"" hours, ""m ""minutes"""))

faz algumas formatações que você deseja, como mostra a tabela abaixo.

O formato dentro da função TEXT() deve ser colocado entre aspas simples, mas as aspas em torno dos rótulos de horas e minutos devem ser "escapadas" usando aspas duplas.

Alémdisso,nãoconseguiexibircorretamenteapenasosminutos.Estafórmulafornece"1 minuto" para todos os valores da coluna A:

=TEXT(A1,"m"" minute""")