☰

Calendário mensal dinâmico em Excel

tag-icon • tag-icon microsoft-excel calendar
Calendário mensal dinâmico em Excel

Quero criar um calendário mensal (1 mês por planilha) onde apenas o ano possa mudar (e assim a data). Meu modelo de calendário já está criado. Tudo que quero é adicionar uma fórmula que altere a data de acordo com o dia da semana quando eu mudar o ano. A semana começa no domingo

Obrigado !

Responder1

Aqui está o que eu descobri. Ajuste as instruções conforme necessário para se adequar ao seu próprio modelo.

Instruções

  1. Defina A1 para o valor do ano do mês que você deseja representar.
    • Para a data da primeira revisão deste post, seria “2015”.
  2. Defina A2 como o nome completo do mês que você deseja representar.
    • Para a data da primeira revisão deste post, seria “maio”.
  3. Preencha A3:G3 com os dias da semana, começando com “Domingo” em A3 e terminando com “Sábado” em G3.
  4. Coloque a seguinte fórmula em A4:=IF(WEEKDAY(DATEVALUE(CONCATENATE($A$2," 1, ",$A$1)))=1,1,"")
    • Dependendo do Mês/Ano, esta fórmula pode retornar um valor em branco aqui. (Será para maio de 2015.) Isso é normal.
  5. Coloque a seguinte fórmula em B4:=IF(A4="",IF(WEEKDAY(DATEVALUE(CONCATENATE($A$2," 1, ",$A$1)))=COLUMNS($A$4:B4),1,""),A4+1)
    • Dependendo do Mês/Ano, esta fórmula pode retornar um valor em branco aqui. (Será para maio de 2015.) Isso é normal.
  6. Copie a fórmula de B4 nas células C4:G4. O Excel deve ajustar automaticamente as referências de células na fórmula conforme necessário.
    • Neste ponto, a fórmula deve ser avaliada como “1” em uma das células da linha 4, e cada célula posteriormente deve retornar um incremento maior que a célula anterior. As células antes do “1” devem ficar em branco. Para maio de 2015, você deverá ver espaços em branco em A4:E4, "1" em F4 (sexta-feira) e "2" em G4 (sábado).
  7. Coloque a seguinte fórmula em A5:=IF(ISERROR(DATEVALUE(CONCATENATE($A$2," ",G4+1,", ",$A$1))),"",G4+1)
    • O resultado da fórmula deve ser um valor superior ao valor de G4.
  8. Copie a fórmula em A5 para baixo nas células A6:A9. O Excel deve ajustar automaticamente as referências de células na fórmula conforme necessário.
    • A6:A9 será avaliado como espaços em branco por enquanto. Isso é normal.
  9. Coloque a seguinte fórmula em B5:=IF(ISERROR(DATEVALUE(CONCATENATE($A$2," ",A5+1,", ",$A$1))),"",A5+1)
    • O resultado da fórmula deve ser um valor superior ao valor de A5.
  10. Copie a fórmula em B5 nas células C5:G5. O Excel deve ajustar automaticamente as referências de células na fórmula conforme necessário.
    • As fórmulas na linha 5 devem agora ser avaliadas como um conjunto de números aumentando gradativamente da coluna A para a coluna G.
  11. Selecione B5:G5 e copie essas células até B6:G9. O Excel deve ajustar automaticamente as referências de células na fórmula conforme necessário.
    • Neste ponto, o calendário deve ser preenchido adequadamente para o mês determinado. As células antes do primeiro dia do mês e depois do último dia do mês devem ficar em branco.
    • Para determinados meses, como janeiro de 2015, a linha 9 ficará totalmente em branco. Isto é normal.

Funções

  • CONCATENARune cadeias de texto.
  • DATAVALORretorna um valor de "número de série" do Excel para a data representada por uma sequência de texto. Ele retornará um erro se a string não corresponder a um formato de data conhecido ou se a string não representar uma data real (por exemplo: "32 de janeiro de 2015" ou "29 de fevereiro de 2015").
  • DIA DA SEMANAretorna um valor, de 1 a 7, representando o dia da semana de uma determinada data.
  • COLUNASretorna o número de colunas abrangidas por uma matriz especificada de células.
  • SEpermite retornar um valor escolhido (ou resultado de fórmula) com base no fato de uma fórmula especificada ser avaliada como VERDADEIRO ou FALSO.
  • ÉERROretorna TRUE se a fórmula fornecida resultar em erro ou FALSE se a fórmula não gerar erro.

Explicações da fórmula

  • A4reúne informações de A1 e A2 para gerar uma sequência de texto representando o primeiro dia do mês. (ex: "1º de maio de 2015".) Em seguida, verifica se o dia da semana daquela data seria domingo. Retorna 1 se for domingo, em branco se não for.

  • B4:G4verifique a célula anterior para ver se está em branco. Caso contrário, retorna um valor um valor superior ao da célula anterior. Se a célula anterior estiver em branco, verifica se o dia da semana atual corresponde ao primeiro dia do mês determinado - retorna 1 em caso afirmativo, em branco se não.

  • A5: A9adicione 1 ao valor do final da linha anterior e combine o resultado com as informações de A1 e A2 para formar uma sequência de datas. Verifica se a data realmente existe. Retorna o número do dia do mês se for o caso, em branco se não for o caso.

  • B5:G9adicione 1 ao valor da célula anterior e combine o resultado com as informações de A1 e A2 para formar uma sequência de datas. Verifica se a data realmente existe. Retorna o número do dia do mês se for o caso, em branco se não for o caso.

Captura de tela

Exemplo de saída para maio de 2015.

insira a descrição da imagem aqui

Responder2

Crie New Workbooka partir do modelo. Calendar sundayseqüência de pesquisa.

String de pesquisa "Domingo do calendário"

Selecioneárea de diase pressione Manage Rulesno Conditional Formattingmenu.

editar menu de regras de formatação do Excel

AdicionarNew Rule

Caixa de diálogo de formatação de regras do Excel

Adicionar regra igual=TODAY()

Excel adicionar regra de formatação igual "=TODAY()"

Resultado:

Regra de formatação do Excel Resultado do calendário

Responder3

Digite estes itens nas células indicadas:

A1: 2015 B1: Primeiro dia C1: =DATEVALUE(A1&"-"&A2&"-01")
A2: 5 B2: Semana-Domingo C2: =C1-WEEKDAY(C1;2)

C4: =TEXTO(COLUNAS($A$4:A4);"Ddd")
C5: =SE(TEXTO($C$2-1+COLUNAS($A$4:A4)+(LINHAS($A$4:A4)-1)*7;"AAAA-MM")=TEXTO($C$1; "AAAA-MM");$C$2-1+COLUNAS($A$4:A4)+(LINHAS($A$4:A4)-1)*7;"")

Em seguida, copie C4 e C5 para preencher a largura de sete (7) células.
Seguido por uma cópia da linha 7 para preencher um total de seis (6) linhas.
Selecione as células nessas últimas 6 linhas e pressione CTRL+ 1, clique em Personalizado no painel esquerdo e insira um único Dpara o formato personalizado.

O resultado ficará assim:

Primeiro dia de 2015 01/05/2015              
5 Semana-Domingo 2015-04-26              

Dom Seg Ter Qua Qui Sex Sáb
                    1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31

Isso funcionará no Excel, LibreOffice e provavelmente em qualquer outra planilha.

Responder4

Linux:

Abra um terminal e digite:

ncal 2015e pressione ENTER(no $prompt).

A saída será semelhante a:

                                  2015
    January           February          March             April             
Su     4 11 18 25     1  8 15 22        1  8 15 22 29        5 12 19 26   
Mo     5 12 19 26     2  9 16 23        2  9 16 23 30        6 13 20 27   
Tu     6 13 20 27     3 10 17 24        3 10 17 24 31        7 14 21 28   
We     7 14 21 28     4 11 18 25        4 11 18 25        1  8 15 22 29   
Th  1  8 15 22 29     5 12 19 26        5 12 19 26        2  9 16 23 30   
Fr  2  9 16 23 30     6 13 20 27        6 13 20 27        3 10 17 24      
Sa  3 10 17 24 31     7 14 21 28        7 14 21 28        4 11 18 25      

    May               June              July              August            
Su     3 10 17 24 31     7 14 21 28        5 12 19 26        2  9 16 23 30
Mo     4 11 18 25     1  8 15 22 29        6 13 20 27        3 10 17 24 31
Tu     5 12 19 26     2  9 16 23 30        7 14 21 28        4 11 18 25   
We     6 13 20 27     3 10 17 24        1  8 15 22 29        5 12 19 26   
Th     7 14 21 28     4 11 18 25        2  9 16 23 30        6 13 20 27   
Fr  1  8 15 22 29     5 12 19 26        3 10 17 24 31        7 14 21 28   
Sa  2  9 16 23 30     6 13 20 27        4 11 18 25        1  8 15 22 29   

    September         October           November          December          
Su     6 13 20 27        4 11 18 25     1  8 15 22 29        6 13 20 27   
Mo     7 14 21 28        5 12 19 26     2  9 16 23 30        7 14 21 28   
Tu  1  8 15 22 29        6 13 20 27     3 10 17 24        1  8 15 22 29   
We  2  9 16 23 30        7 14 21 28     4 11 18 25        2  9 16 23 30   
Th  3 10 17 24        1  8 15 22 29     5 12 19 26        3 10 17 24 31   
Fr  4 11 18 25        2  9 16 23 30     6 13 20 27        4 11 18 25      
Sa  5 12 19 26        3 10 17 24 31     7 14 21 28        5 12 19 26

... com a data atual marcada com um número de "bloco invertido". Copie e cole como texto.
A quantidade de trabalho necessária depende da ferramenta que você usa.

editar: Digitando no prompt do shell Bash;
$ y=2015; for (( i=1 ; i<13 ; i++)) do cal $i $y ; done | sed -re 's/([^ ]+ 2015|[a-zåäö]{2}|[ 0-9]{2} )/"\1",/g' | sed -re 's/"\,[ ]*$/"/' >calendar-$y.csv

Cria um arquivo CSV que pode ser carregado em qualquer planilha. altere y=2015para refletir o ano para o qual você deseja criar um calendário.

informação relacionada