Fórmula para exibir o mês do projeto e o número do ano, em vez do mês e ano do calendário no Excel

Question

Desejo exibir datas como mês do projeto # e / ou ano # (ou seja, ano 1 Mo. 1), em vez de mês e ano do calendário.

Estou usando um modelo de modelagem financeira que atualmente permite inserir a data de início em uma célula (D4) e define automaticamente todas as datas subseqüentes nos formatos relevantes, mas como a data de início é um destino em movimento, eu gostaria de alterá-lo para refletir o mês do projeto e o número do ano, em vez do mês e ano do calendário.

• Célula D4 - Exibe mês & ano de data de início (entrada manual)

• Células E4: BL4 - Exibir mês & ano. Fórmula de amostra para a célula E4:

      =DATE(YEAR(D4),MONTH(D4)+1,1)
  

• Células BN4: CG4 - Exibem quarter # & ano. Fórmula de amostra para a célula BN4:

      =$CI$4
  

• Células CI4: CM4 - Exibir ano. Fórmula de amostra para a célula CI4:

      =DATE(YEAR(E4),MONTH(1),DAY(1))
  

Answer 1

A teoria

Se você quiser que o Excel faça a matemática das datas com suas funções internas, provavelmente seria mais simples manter as fórmulas existentes como estão e exibir outras células que calculam o mês e o ano do projeto. Você pode ocultar as datas "primárias" do Excel, se desejar, ou apenas usar uma parte diferente da planilha ou outra planilha para exibir a saída desejada. Caso contrário, todas as células de data precisarão de uma fórmula personalizada que faça cálculos manuais de data e, se os cálculos precisarem considerar o tempo no nível do dia (veja abaixo), talvez você precise de formatação personalizada sofisticada.

Datas do projeto: O tempo decorrido é apenas subtração de data, exceto que as datas do projeto geralmente começam com 1 em vez de 0 (ou seja, uma tarefa que acontece no primeiro dia é dia 1; a subtração da data renderia zero), portanto, é necessário ajustar a data de "início" usada para esses cálculos ou adicionar 1 ao dia, mês e ano de cada data do projeto calculada. A abordagem mais simples é usar uma data inicial ajustada. Exatamente como você ajusta isso depende da convenção de horário que você usa, conforme descrito abaixo.

Convenção de tempo do projeto: Os meses do projeto serão meses do calendário ou o tempo decorrido? Por exemplo, digamos que o projeto comece em 29 de junho. Uma tarefa é realizada em 1º de julho do mês 1 do tempo decorrido do projeto ou no segundo mês, porque junho é o primeiro mês? O mesmo se aplica ao ano. Se você quiser basear o mês do projeto no mês, poderá usar o número do mês e do ano do Excel para fazer as contas.

Se você quiser usar a duração decorrida, precisará calcular a partir do número do dia do Excel e convertê-lo em meses e anos. Para projetos curtos, você estará por perto se usar apenas 30 dias como um mês. Para projetos longos, você pode querer aproximar-se da duração do ano calendário fazendo algo como usar os dias por mês dos meses de calendário associados para calcular os meses do projeto decorridos. Se o seu desejo é uma medida consistente do tempo em que as datas do projeto se movem, provavelmente é melhor usar 30 dias como um mês do projeto e apenas reconhecer que a cada ano será quase uma semana a menos.

Implementação

Então, vamos ver como você implementaria isso.

• Mês do projeto / ano = Mês / ano do calendário: Se você pretende basear meses e anos do projeto em meses e anos do calendário, eu armazenaria uma data "básica" igual a um ano e um mês antes da data de início do seu projeto. Você não quer mudar o dia neste caso porque isso poderia colocá-lo no mês errado. Portanto, se a sua data de início real for na célula D4, sua data de cálculo seria:

  = DATA (ANO (D4) -1, MÊS (D4) -1, DIA (D4))

  Para fins de ilustração, digamos que esteja na célula Z1. Agora, todos os cálculos da data do projeto são encontrados subtraindo esse valor da data real do projeto. Então diga que você tem uma data de evento na célula E4. Você mostraria a data do projeto em uma célula associada em algum lugar como:

  ="Yr. "&YEAR(E4)-YEAR(Z1)&"  Mo. "&MONTH(E4)-MONTH(Z1)
  

  Um evento no primeiro mês seria exibido como Yr. 1 Mo. 1 .

• Mês do projeto = 30 dias: Se você quiser que o tempo do projeto represente a duração independente do calendário, será necessário um pouco mais de data. As datas são armazenadas internamente como o número de dias desde a data base, para que você possa trabalhar diretamente a partir disso. Nesse caso, a data base de cálculo seria de um ano e um mês antes da data de início, que é de 390 dias usando meses de 30 dias. Assim, a data de cálculo armazenada em Z1, neste caso, seria:

  = D4-390

  (Editar: originalmente também ajustado para o "Dia 1", mas como você não está trabalhando no nível do dia, esse ajuste não é necessário e eu o removi.)

  Novamente, diga que a data do seu evento está na célula E4. Seu ano do projeto é: INT((E4-Z1)/360) .

  Para o mês do projeto, precisamos ignorar anos inteiros, que já foram contabilizados, então o mês do projeto é: INT((E4-Z1)/30)-12*INT((E4-Z1)/360) . INT é a função inteira que trunca para números inteiros. Assim, a data do projeto exibida, usando a mesma formatação de antes, seria:

  ="Yr." & INT ((E4-Z1) / 360) & "Mo." & INT ((E4-Z1) / 30) -12 * INT ((E4-Z1) / 360 )

Answer 2

A maneira mais simples de fazer isso é usar a Funcionalidade numérica "Personalizada". Você pode encontrar isso no Excel, selecionando Ctrl + F1 em uma célula e criando em Personalizar um formato numérico com a aparência desejada. Na memória, acho que o número do mês é 'mm' ou 'MM'. Se você pesquisar a função de ajuda, encontrará.