Esta é a melhor estratégia para exibir um valor de tempo negativo no Excel?

Question

Estou tentando enviar uma célula calculada como duedate - now() para quantificar o tempo vencido versus o tempo restante (valores negativos = vencidos, valores positivos = tempo restante). Considere:

      Col A DueDate    Col B  NOW         Col C remaining time
Row1  3/7/2013  9:00   3/8/2013   12:00   -1:03:00:00
Row1  3/8/2013 15:00   3/8/2013   12:00    0:03:00:00
Row3  3/9/2013  9:00   3/8/2013   12:00    1:03:00:00

No entanto,

• Esta folha liga-se a muitas outras folhas usando o sistema de datas 1900, por isso estou desconfiado sobre a mudança para o sistema 1904 por causa do imprevisibilidade de como as datas serão interpretadas entre os dois sistemas de data.
• A1-B1 rende ################## , como esperamos quando a data retorna uma data negativa.
• Eu encontrei uma conversão de texto, TEXT(ABS(A1-B1),-h:mm:ss , mas porque é simplesmente adicionar um negativo ao valor absoluto, CADA valor de data é retornado como negativo. ABS está correto, mas não o sinal.

Usando isso como uma pista, eu tentei TEXT((A1-B1),h:mm:ss , mas recebi VALUE!# ; Estou assumindo que não gosta do resultado negativo da matemática do tempo.

Isto: IF(B1>A1,(TEXT(ABS(A1-B1),-h:mm:ss,A1-B1) tem alguns problemas.

• Qualquer coisa devida hoje é retornada como 0:00:00:00 ; parece ser ignorando horas, minutos e segundos.
• A classificação da coluna na hora aparece para classificar em ABS , então minha Os valores "0: 00: 00: 00" estão no topo da lista e os maiores números negativos na parte inferior. Eu esperaria que um tipo normal teria o maior negativo, depois o zero, então subindo para o maior valor.

EDITAR:

A sugestão de Martineau me aproximou, mas qualquer coisa devida nos próximos dias ou dois é esquisita. Modifiquei h to [h] para contas maiores que 24 horas.

DueDate         NOW()           
3/1/2013 17:00  3/8/2013 12:16  -163:16:11
3/7/2013 17:00  3/8/2013 12:16  -19:16:11
3/8/2013 12:16  3/8/2013 12:16  992196:16:11    <-- ???
3/15/2013 17:00 3/8/2013 12:16  992369:00:00   <--  ???
3/31/2013 17:00 3/8/2013 12:16  992753:00:00   <--  ???
5/8/1944 12:00  3/8/2013 12:16  -603384:16:11
6/14/2238 7:00  3/8/2013 12:16  2966839:00:00
3/8/2013 9:00   3/8/2013 12:16  -3:16:11
3/8/2013 17:00  3/8/2013 12:16  992201:00:00   <--  ???

No entanto, com h como h , as mesmas linhas retornam valores quadrados:

DueDate         NOW()       3/1/2013 17:00  3/8/2013 12:23  -19:23:45
3/7/2013 17:00  3/8/2013 12:23  -19:23:45
3/8/2013 12:23  3/8/2013 12:23  12:23:45     <-- ???
3/15/2013 17:00 3/8/2013 12:23  17:00:00     <-- ???
3/31/2013 17:00 3/8/2013 12:23  17:00:00     <-- ???
5/8/1944 12:00  3/8/2013 12:23  -0:23:45
6/14/2238 7:00  3/8/2013 12:23  7:00:00
3/8/2013 9:00   3/8/2013 12:23  -3:23:45
3/8/2013 17:00  3/8/2013 12:23  17:00:00     <-- ???

Existe uma maneira mais elegante de formatar a coluna C da maneira que eu gostaria de ver?

    

Answer 1

Acho que essa fórmula funcionará:

=IF(B1>A1, TEXT(B1-A1,"-h:mm:ss"), TEXT(A1-B1,"h:mm"))

Mas apenas para diferenças de tempo inferiores a 24 horas. Por períodos mais longos, você pode usar:

=IF(B1>A1, "-",) & TEXT(INT(ABS(B1-A1)), "#,##0") & "d " & TEXT(MOD(ABS(B1-A1), 1), "hh:mm")

para um resultado como este (eu justifiquei o tempo restante):

DueDate             Now                 Remaining Time
03/01/2013 17:00    03/08/2013 12:16         -6d 19:16
03/07/2013 17:00    03/08/2013 12:16         -0d 19:16
03/08/2013 12:16    03/08/2013 12:16          0d 00:00
03/15/2013 17:00    03/08/2013 12:16          7d 04:44
03/31/2013 17:00    03/08/2013 12:16         23d 04:44
05/08/1944 12:00    03/08/2013 12:16    -25,141d 00:16
06/14/2238 07:00    03/08/2013 12:16     82,276d 18:44
03/08/2013 09:00    03/08/2013 12:16         -0d 03:16
03/08/2013 17:00    03/08/2013 12:16          0d 04:44

Provavelmente poderia ser expandido para lidar com anos também, se necessário. Fazer isso com total precisão seria um pouco mais complicado, já que o número de dias em um ano varia ¹ - então, isso será deixado como um exercício para o leitor. ;-)

[1] Segundo a Wikipedia, o ano civil médio é de 365,2425 dias.

    

Answer 2

Se você deseja que o excel exiba tempos negativos, mesmo ao fazer cálculos com eles, é possível alterar como os horários são criados. Você pode fazer isso indo até Ferramentas, Opções, Guia Cálculos e selecionando o 1904 Date System. Isso agora exibirá tempos negativos como -xx: xx: xx