☰

Fórmula Excel para calcular a data com base em 2 células e como copiar para outras células?

tag-icon • tag-icon microsoft-excel worksheet-function date
Fórmula Excel para calcular a data com base em 2 células e como copiar para outras células?

Usuário iniciante do Excel aqui. Aqui está uma versão simplificada da minha planilha:

start date  end date    eday    duration
                        -21     4
                         12     0

begindate = "03/03/2020"

Quero criar fórmulas para calcular a data de início e a data de término com base nos valores nas colunas eday e duração e na data de início:

start date = begindate + eday
end date = start date + duration

Portanto, para o exemplo acima, se eu aplicar uma fórmula, esperaria o seguinte resultado:

start date  end date    eday    duration
02/11/2020  02/11/2020  -21     4
03/15/2020  03/19/2020   12     0

a data de início 03/03/2020será sempre a mesma.

Consegui fazer uma fórmula funcionar, mas quando uso a alça de preenchimento ela não copia a fórmula corretamente.

Por favor, ajude um novato com estas perguntas:

  1. Qual é o melhor lugar para armazenar a data de início "03/03/2020" - uma planilha separada? codificá-lo? outro?

  2. Qual é a fórmula para data de início e data de término?

  3. Como copio a fórmula em todas as linhas da coluna

Muito obrigado.

Responder1

A primeira coisa que gosto de lembrar sobre datas e horas no Excel é que na verdade são apenas números, que são formatados para se parecerem com datas/horas usando formatação numérica. A data de hoje (07 de setembro de 2019) é 43715 e a hora atual (12h20) é 0,14302083...

Existem funções de planilha que podem obter o número mágico (essencialmente dias desde 01 de janeiro de 1900) para você, como DATE(), TODAY()ou NOW().

Para isso, supondo que seuhojecoluna é Ce seus dados começam em linha 2, você pode inserir seudata de iníciofórmula em A2como =DATE(2020,3,3)+$C2e seudata finalfórmula em B2como =$A2+$D2. Essa é a abordagem mais simples.

Observe que usei referências absolutas de coluna ( $C, $A, $D) para forçar o Excel a sempre procurar em colunas C, Ae D, independentemente de onde copio/colo ou preencho, e usei referências relativas de linha ( 2) para que, se eu copiar/colar ou preencher, o número da linha mudará como parte do preenchimento (ou seja, na linha 3, a fórmula seria lida =$A3+$D3). Às vezes você quer isso, às vezes não, então você deve experimentar $e não $para ver o que acontece.

Eu suspeito que você originalmente colocou seudata de iníciovalor em uma célula, mas usou uma referência relativa em sua fórmula e, quando você preencheu, a referência relativa para seudata de iníciomudado. Se eu estiver certo, então você precisaria de uma referência absoluta para o seudata de iníciocélula, por exemplo, se estivesse na coluna Jrow 1, sua fórmula deveria ser $J$1, não J1.

À medida que você fica mais confiante, pode usar recursos mais complicados para torná-lo mais resiliente ou configurável. Por exemplo:

  • Você pode usar oGerenciador de nomespara armazenar sua data fixa e constante, criando um novo nome chamado BeginDatee definindo seu valor como =DATE(2020,3,3). Agora você pode atualizar sua fórmula de data de início A2e =BeginDate+$C2esse valor fixo será usado em todos os lugares.

  • Você pode formatar seu intervalo de quatro colunas como umTabela com cabeçalhos, o que permitirá que você use esses nomes de cabeçalho em vez de referências de coluna. Se você formatar o intervalo como uma tabela, na primeira linha de dados do seudata de iníciocoluna (que era A2) você pode inserir =BeginDate+[eday]e em seudata finalcoluna (que era B2) você pode inserir =[start date]+[duration]. Por se tratar de uma tabela, ambas as fórmulas devem ser preenchidas automaticamente, à medida que a tabela cresce. Agora aplique um formato numérico simples deEncontro curtopara o seudata de inícioedata finalcolunas.

  • Você pode interromper a exibição de datas calculadas em sua tabela quando não tiver inserido nenhum valor edayou durationalterando sua fórmula emdata de iníciopara o mais complicado:

    =IF([eday]="", "", BeginDate+[eday])

    ...e alterando a fórmula emdata finalpara:

    =IF(OR([start date]="",[duration]=""), "", [start date]+[duration])

informação relacionada