Formato personalizado do Excel: data / hora escrita?

Question

Estou tentando formatar um datetime no Excel 2010 para exibir algo assim:

1.234 ==> 1 day, 5 hour, 36 minutes

Eu sei fazer algo semelhante para cenários mais simples

Por apenas alguns dias, posso usar um formato condicional da seguinte forma:

[=1] d" day";d" days"

Mas isso não funciona com um timestamp.

Para todo o conjunto, posso fazer:

d" days, "h" hours, "m" minutes"

mas isso parece engraçado quando um desses valores (geralmente dias) é 1 .

Não sei como fundir esses dois conceitos. Eu seria muito bom ter

1 day, 5 minutes

se o valor das horas for 0, mas

1 day, 0 hours, 5 minutes

funciona igualmente bem.

Eu posso preparar uma solução com o VBA / UDF, mas não há outro código na pasta de trabalho neste momento, então gostaria de evitar essa rota, se possível. Eu também sei que posso analisar o valor em outra célula como esta:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & ", " & etc...

Mas isso é desajeitado e requer a execução de cálculos em um novo conjunto de células, em vez de apenas inserir o formato na coluna de dados atual e estar pronto.

Isso é realmente possível com um formato de número condicional, ou estou preso a um método mais longo e demorado?

    

Answer 1

Com base no feedback aqui, parece que isso é demais para um formato simples. Em vez disso, fui em frente e produzi algumas fórmulas para criar isso.

Para exibir todos os valores, incluindo todos os valores 0:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & ", " & MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & ", " & SECOND(A1) & IF(SECOND(A1)=1," second"," seconds")

1.25001 : 1 day, 6 hours, 0 minutes, 1 second
2.25001 : 2 days, 6 hours, 0 minutes, 1 second
1.04167 : 1 day, 1 hour, 0 minutes, 0 seconds

Para remover somente os valores 0:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & IF(HOUR(A1) + MINUTE(A1) + SECOND(A1)>0, ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & IF( MINUTE(A1) + SECOND(A1)>0, ", " & MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & IF( SECOND(A1)>0, ", " & SECOND(A1) & IF(SECOND(A1)=1," second"," seconds"), ""), ""), "")

1.25001 : 1 day, 6 hours, 0 minutes, 1 second
2.25001 : 2 days, 6 hours, 0 minutes, 1 second
1.04167 : 1 day, 1 hour

Se todos os valores 0 forem omitidos, isso funcionará:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & IF(HOUR(A1) + MINUTE(A1) + SECOND(A1)>0, ", ", "") & IF(HOUR(A1)>0,HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & IF( MINUTE(A1) + SECOND(A1)>0, ", ", ""),"") & IF(MINUTE(A1)>0, MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & IF( SECOND(A1)>0, ", ", ""),"") & IF(SECOND(A1)>0,SECOND(A1) & IF(SECOND(A1)=1," second"," seconds"),"")

1.25001 : 1 day, 6 hours, 1 second
2.25001 : 2 days, 6 hours, 1 second
1.04167 : 1 day, 1 hour

    

Answer 2

Você não pode fazer isso com um formato de número personalizado , é muito complicado para condicionais simples como [= 1] e não pode usar nenhuma função na caixa Tipo.

Você precisa de uma fórmula em uma célula separada. (Você poderia, suponho, ocultar a coluna original.)