Fórmula do Excel para subtração automática de data / hora

Question

Eu quero fazer o seguinte na célula K14: Se a célula D14 contiver C17 , pegue a célula F14 e subtraia 3 horas e 45 minutos.

O formato de data / hora que estou usando é uma data / hora do calendário juliano, portanto 16271/1845 menos as 3 horas e 45 minutos seria 16271/1500 . Esses valores de data / hora são armazenados como uma cadeia de texto. A data juliana é o ano com dois dígitos seguido pelo dia com três dígitos do ano.

    

Answer 1

Aqui está uma solução para você com base no problema conforme definido originalmente:

AcélulaA1contémumadata/horadeamostranoseuformato.

B1traduzissoparaumadata/horadoExcelcomestafórmula:

=DATE(LEFT(A1,2),1,1)+MID(A1,3,3)-1+MID($A1,7,2)/24+RIGHT($A1,2)/1440

Issousafunçõesdetextoparaanalisaraspartesecriaadatadoano,usando1ºdejaneiro,depoisadicionaacontagemdedias(esubtrai1,jáqueoanonãopodecomeçarem0dejaneiro).ComooExcelarmazenaostemposcomofraçãodeumdia,ovalordahoraédivididopor24eovalordominutoédivididopor1440minutosemumdia.

Agora,suaentradaestáemumformulárionoqualoExcelpodefazeradata/horadamatemática.Essaéaabordagemmaislimpa,jáquevocêpoderiaencontrarvaloresemque,deoutraforma,precisariaajustaracontagemdediasouoano,efazeramatemáticadepoder-de-deznosmomentosficaconfuso.

C1contémadata/horaapósasubtraçãode3horase45minutos:

=B1-TIME(3,45,0)

D1traduzoresultadodevoltaparaoseuformato:

=TEXT(C1,"yy")&TEXT(INT(C1)-DATE(YEAR(C1),1,0),"00#")&"/"&TEXT(MOD(C1,1),"hhmm")

Ele concatena as partes usando & para construir a string.

• O dia com três dígitos é a data do resultado menos "janeiro 0" do ano do resultado na mesma hora do dia (o horário é parte do valor armazenado e o arredondamento pode afetar a contagem de dias). A função INT extrai a data do resultado à meia-noite para corresponder à hora da data calculada de 0 de janeiro.
• A função MOD fornece a parte fracionária do resultado, que é a parte do tempo, e usa a função TEXTO para criar as horas e minutos exibidos no formato de 24 horas.

Você poderia fazer isso tudo em uma célula difícil de manter, substituindo as fórmulas por referências de célula. Em D1, substitua cada referência C1 pela fórmula em C1 após o% inicial = . Em seguida, substitua cada referência B1 pela fórmula em B1 após o = inicial.

Sua pergunta descreve uma condição IF, mas você não fornece informações suficientes para ela. Substitua F14 em vez de A1 em sua fórmula para se referir à célula de entrada correta. O IF na célula K14 ficaria assim:

=IF(D14="C17",formula based on above answer,"")

Não duplique o = inicial da minha fórmula de resposta ao inserir a fórmula da resposta no FI. Eu assumi um nulo (as aspas vazias, que lhe dará uma célula em branco), para a falsa condição, mas você pode fazer o que quiser.