Formato de tempo decorrido no Excel

Question

No Excel, dado um número que representa um determinado período de tempo (por exemplo, 25,5 horas), existe uma maneira de formatá-lo para que seja mostrado como 1d 1h 30m?

Pergunta de recompensa:

Existe uma maneira de adicionar meses a esse formato? por exemplo. 1M 1d 1h 1m. O problema com o formato atual é que ele não manipula nada durante 32 dias (isto é, 32,5 é formatado como 1d 8h 0m)

    

Answer 1

Você precisará converter essas horas em algo que o Excel possa entender.

Supondo que suas horas estejam no formato decimal (por exemplo, 25,5 horas, 37,25 horas), veja como você pode fazer isso:

Step1:
Divida o valor por 24.
Isto obtém a relação de horas / dia, ou converte a quantidade para uma fração de um dia (por exemplo, 25 horas convertidas para 1 1/24 ou 1,04).

Step2:
Alterar o formato numérico da célula que contém a fórmula ou o valor convertido para Personalizado
(pressione Ctrl + 1 > Personalizado). Use esta string de formato:

[<=0.0416551] [m]"m";[<1]h"h" m"m";d"d" h"h" m"m"

0.0416551 = 0:59:59 em decimal

ou

[<1]h"h" m"m"; d"d" h"h" m"m"

ou

d"d" h"h" m"m"

Exemplo :

    

Answer 2

Se a hora estiver em horas, então esta é uma maneira:

=TRUNC(A1/24) & "d " & TRUNC(A1-TRUNC(A1/24)*24) & "h " & 
       60*((A1-TRUNC(A1/24)*24)-TRUNC((A1-TRUNC(A1/24)*24))) & "m"

    

Answer 3

(não é possível adicionar como comentário, pois não tenho 50 reputação)

Para obter meses para exibir, você pode usar o seguinte formato (observe que isso não atende diretamente às suas necessidades exatas, mas você pode modificar isso conforme apropriado). Eu também adicionei algumas cores às minhas strings para esclarecer os intervalos de tempo: No exemplo de topo Vermelho por mais de um dia, preto por dentro de um dia. O segundo exemplo tem vermelho para o ano +, azul-verde para entre um quarto e um ano e azul para outro. É claro que você pode usar diferentes especificações de formatação conforme desejar.

[Red][>1]yy\y mm\m dd\d hh:mm:ss;[Black][<=1]hh:mm:ss;[Red]-0.000000;[White]@ ou [Red][>=364.5]yy\y mm\m dd\d;[Color50][<=122]mm\m dd\d;[Color5]mm\m dd\d;[Black]@

Para formatar "32,5" como 32d 12:00:00 você pode usar [d]\d hh:mm:ss , observe os colchetes, que indicam que você deseja incluir valores maiores que 31 para o número de dias.

A notação instrui o Excel a incluir especificamente a letra após o símbolo \, em vez de interpretá-la como metadados para o comando (por exemplo, d, sem barra, apenas produziria novamente os dias imediatamente). Isso é equivalente à notação "" que você vê em outros exemplos.

Algumas limitações e / ou erros:

1) você não pode especificar mais de 4 tipos de formatação, o último dos quais precisa ser apenas para texto. Isso significa que apenas 3 são utilizáveis para formatação de datas, a terceira das quais não pode conter nenhuma comparação maior / menor do que as comparações.

2) o mm como meses é realmente alto em 1 ao fazer uma diferença de data (no Excel 2010), pois o Excel realmente armazena diferenças de data como DateTimes completos e não há 0º mês, mas janeiro é o primeiro mês. Os dias não parecem ter esse problema, já que "1.1" é exibido como 1 dia e 0,9 será exibido como 0 dia (ambos com 1 mês: P). Eu não sei como fazer matemática dentro do diálogo de estilo de célula (por exemplo, (mm-1) \ m), então se você realmente quiser que esses números sejam precisos (subtraia 1 do mês) você terá que escrever um vB script para convertê-lo e exibir a string de texto final.

3) O Excel não lida adequadamente com diferenças de horário negativas

Como alternativa, você pode fazer a formatação na própria célula com uma fórmula como

=IF((B2-A2)>1,DATEDIF(A2,B2,"y")&"y "&TEXT(DATEDIF(A2,B2,"ym"),"00")&"m "&TEXT(DATEDIF(A2,B2,"md"),"00")&"d",TEXT(A2-B2,"hh:mm:ss"))

Isso exibirá as informações como hh: mm: ss se for menor que um dia e y MM dd se for maior que um dia. Isso não estará sujeito ao erro off-by-one-month como acima. No entanto, essa célula é, na verdade, texto e não é mais um valor numérico. Então você não pode (diretamente) fazer qualquer outra matemática com isso.