Pessoal, hoje vou escrever para vocês uma questão levantada por um leitor deste site. Já o respondi via coments mas resolvi publicar a todos vocês para poder ajudá-los também.
A seguir transcrevo a pergunta feita pelo leitor Luciano em 22 de junho de 2010.
Parabens pelo site, muito bom.
Poderia por favor me ajudar.
Na planilha excel, ao digitar o mes “janeiro/2010”, gostaria que aparecesse em uma coluna todos os “dias” daquele mes, na segunda coluna os “dias da semana” e na terceira coluna “feriado” quando for. ex:
A1 = Janeiro/2010
A2 = 1
B2 = Sexta-feira
C2 = Se for feriado aparece a palavra Feriado
A3 = 2
B3 = Sábado
C3 =
Grato
Elaborei a seguinte solução:
Primeiramente, indico a leitura prévia dos seguintes artigos:
Um pouco mais sobre datas – Parte 1
Um pouco mais sobre datas – Parte 2
Um pouco mais sobre datas – Parte 3
Um pouco mais sobre datas – Parte 4
Bem, a idéia seria digitar o mês e ano na célula A1 e listar, automaticamente, os dias, dias da semana e feriados daquele mês. Ou seja, se o conteúdo da célula A1 fosse Janeiro/2010, nas células abaixo de A1 seriam listados todos os dias deste mês em formato numérico sequencial (1,2,3,4,5,…,31). E na coluna B nas linhas adjacentes seriam informados os dias da semana (domingo, segunda-feira, terça-feira,…). E finalmente, na coluna C, seria mostrado quais dias daqueles seriam feriados.
Então. Crie uma nova planilha excel e siga os passos abaixo.
1o. passo:
Acesse o ambiente de programação em VBA (ALT+F11) e adicione um módulo.
Nesse módulo insira o código abaixo transcrito.
Public Function VerificaSeFeriado(dDataX As Date) As Boolean
Dim FeriadosFixos(7) As Date
Dim FeriadosMoveis(2) As Date
Dim iAnoX As Integer
Dim dPascoa As Date
iAnoX = Year(dDataX)
dPascoa = CalculaPascoa(iAnoX)
FeriadosFixos(0) = CDate("1/1/" & iAnoX) 'Confraternização Universal
FeriadosFixos(1) = CDate("21/4/" & iAnoX) 'Tiradentes
FeriadosFixos(2) = CDate("1/5/" & iAnoX) 'Trabalho
FeriadosFixos(3) = CDate("7/9/" & iAnoX) 'Independência do Brasil
FeriadosFixos(4) = CDate("12/10/" & iAnoX) 'Nossa Senhora Aparecida
FeriadosFixos(5) = CDate("2/11/" & iAnoX) 'Finados
FeriadosFixos(6) = CDate("15/11/" & iAnoX) 'Proclamação da Repúplica
FeriadosFixos(7) = CDate("25/12/" & iAnoX) 'Natal
FeriadosMoveis(0) = DateAdd("d", -2, dPascoa) 'Sexta Paixão
FeriadosMoveis(1) = DateAdd("d", -47, dPascoa) 'Carnaval
FeriadosMoveis(2) = DateAdd("d", 60, dPascoa) 'Corpus Christi
Select Case dDataX
Case FeriadosFixos(0), FeriadosFixos(1), FeriadosFixos(2), FeriadosFixos(3), FeriadosFixos(4), FeriadosFixos(5), FeriadosFixos(6), FeriadosFixos(7)
VerificaSeFeriado = True
Case FeriadosMoveis(0), FeriadosMoveis(1), FeriadosMoveis(2)
VerificaSeFeriado = True
Case Else
VerificaSeFeriado = False
End Select
End Function
Private Function CalculaPascoa(iAno As Integer) As Date
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim G As Integer
Dim H As Integer
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim M As Integer
Dim N As Integer
Dim P As Integer
Dim Q As Integer
Dim R As Integer
Dim S As Integer
A = iAno \ 100 'o inteiro de (Ano ÷ 100)
B = iAno Mod 19 'o resto de (Ano ÷ 19)
C = (A - 17) \ 25 'o inteiro de [(A - 17) ÷ 25]
D = A \ 4 'o inteiro de (A ÷ 4)
E = (A - C) \ 3 'o inteiro de [(A - C) ÷ 3]
F = (A - D - E + (19 * B) + 15) Mod 30 'o resto de {[A - D - E + (19xB) + 15] ÷ 30}
G = F \ 28 'o inteiro de (F ÷ 28)
H = 29 \ (F + 1) 'o inteiro de [29 ÷ (F + 1)]
I = (21 - B) \ 11 'o inteiro de [(21 - B) ÷ 11]
J = G * H * I
K = F - (G * (1 - J))
L = iAno \ 4 'o inteiro de (Ano ÷ 4)
M = (iAno + L + K + 2 - A + D) Mod 7 'o resto de [(Ano + L + K + 2 - A + D) ÷ 7]
N = K - M
P = (N + 40) \ 44 'o inteiro de [(N + 40) ÷ 44]
Q = 3 + P
R = Q \ 4 'o inteiro de (Q ÷ 4)
S = N + 28 - (31 * R)
CalculaPascoa = CDate(S & "/" & Q & "/" & iAno)
End Function
2o. passo:
Na célula A1, ao invés de digitar Janeiro/2010, por exemplo, digite 1/1/2010. Faça semelhante para qualquer mês, apenas modificando o mês e o ano. Sempre com o dia 1. E para exibir “Janeiro/2010”, formate a célula de modo personalizado através da string [$-416]mmmm/aaaa;@. Ok.
3o. passo:
Na célula A2, digite a fórmula seguinte e depois copie-a até a linha 32 da mesma coluna.
=SE(MÊS($A$1+LIN()-LIN($A$1)-1) = MÊS($A$1);$A$1+LIN()-LIN($A$1)-1;””)
Depois, formate o intervalo de A2 a A32 com a string presonalizada d;@.
4o. passo:
Na célula B2, digite a fórmula seguinte e depois copie-a até a linha 32 da mesma coluna.
=A2
Em seguida, formate este intervalo com a string personalizada dddd.
5o. passo:
Na célula C2, digite a fórmula seguinte e depois copie-a até a linha 32 da mesma coluna.
=SE(A2=””;””;SE(VerificaSeFeriado(A2);”Feriado”;””))
Muito bom, né. Espero que seja útil para vocês.
Até breve.
Parabéns, são poucos que dividem suas experiencias e conhecimentos.
Lá no final onde define as variáveis A, B, C, etc. Em algumas delas, onde ocorrem divisões, a barra deitada (“/”) não apareceu.
Ex: ” A = iAno 100 ”
O certo seria ” A = iAno / 100 “.
Tem vários erros desses.
Obrigado.
Valeu Marcelo.
Realmente tem vários erros destes. Ainda não consegui um visualizador de códigos bom para colocar no meu site. A digitação é toda correta, mas o script da página estraga vários caracteres do código. Por isso, sempre disponibilizo o arquivo fonte para download. Nele está todo o código sem alterações.
Abração.
Boa tarde;
Gostaria primeiramente de parabeniza-lo pelo excelente trabalho que você está fazendo neste site, que não deixa nada a desejar a outros bons sites que conheço na França / USA, parabens novamente.
Meu amigo, você poderia me ajudar em um problema que está me perseguindo a muito tempo e ainda não encontrei uma boa solução:
Eu preciso identificar em um planilha de datas (tipo cronograma) a número da semana (por ex.: 22/07/2010 = semana 29), utilizando as funções NÚMSEMANA()ou Weeknum()obtenho o resultado (30), pois excel utiliza o principio americano para datas.
Eu necessito de uma ajuda para encontrar o correto número da semana com as seguintes condiçoes:
– O primeiro dia da semana deve ser um domingo
– A primeira semana do ano deve ter pelo menos 4 dias, ou seja se dia 01/Jan cair de domingo à quarta-feira esta seria a Semana 1, caso contrário (de quinta à sábado)esta seria então a última semana do ano anterior (Semana 52)
A formula abaixo é a que utilizo e que se aproxima das minhas necessidades
=SE(NÚMSEMANA(A1;1)-1=0;NÚMSEMANA(A1;1);NÚMSEMANA(A1;1)-1)
Onde A1 = data que necessito identificar a semana
Agradeço pela sua grande e importante ajuda.
Abraços
Ete
Obrigado Etevaldo,
Resolva usando uma fórmula personalizado com VBA.
Inicie o VBA (ALT+F11). Insira um módulo.
Crie a função abaixo:
Public Function NumeroDaSemana(ByVal rngData As Range) As Integer
If rngData.Rows.Count = 1 And rngData.Columns.Count = 1 Then
If IsDate(rngData.Value) Then
NumeroDaSemana = Format(rngData.Value, “ww”, vbSunday, vbFirstFourDays)
Else
NumeroDaSemana = 0
End If
Else
NumeroDaSemana = 0
End If
End Function
Encerre o VBA e volte a planilha.
Digite na célula que você quer mostrar o resultado:
=NumeroDaSemana(A1)
Pronto.
Não consegui configurar apenas por fórmulas prontas, por isso sugiro utilizar a macro acima.
Abraço.
Ola, muito boa essa planilha…
Teria como fazer uma planilha dessa só que começando com o dia 21 de cada mês e terminando dia 20.
Ex.:
21 de janeiro
21 sabado
22 domingo
23 segunda
24 terça
25 quinta
26 sexta
27 sabado
28 domingo
29 segunda
30 terça
31 quarta
01 quita
02 sexta
03 sabado
04 domingo
05 segunda
06 terça
07 quarta
08 quinta
09 sexta
10 sabado
11 domingo
12 segunda
13 terça
14 quarta
15 quinta
16 sexta
17 sabado
18 doming
19 segunda
20 terça
Desde já muito obrigado
Junior,
=SE(MÊS($B$2+LIN()-LIN($B$2)-2+21) = MÊS($B$2)+1;SE(DIA($B$2+LIN()-LIN($B$2)-2+21) <21;$B$2+LIN()-LIN($B$2)-2+21;””);$B$2+LIN()-LIN($B$2)-2+21)
Os 21 que estão em negrito, é a dia inicial, que, dependendo de como queria automatizar a planilha você pode definir numa célula a parte e adaptar na fórmula.
Abç
Era isso mesmo…
Muito obrigado…
=SE(A2=””;””;SE(VerificaSeFeriado(A2);”Feriado”;””))
o meu excel 2003 diz q a formula esta errada, sera q poderia ajudar a me dizer onde estou errando.
se puder ajudar segue o linck da que eu to tentando fazer.
https://skydrive.live.com/redir.aspx?cid=b1ab94e13a8e512a&resid=B1AB94E13A8E512A!759&parid=B1AB94E13A8E512A!571&authkey=!AIkUlX4OuvTqZKM
grato pela atenção
Boa tarde Romeu.
Obrigado pela visita! Aproveito para convidá-lo a seguir @exceldoseujeito no twitter, caso ainda não esteja nos seguindo.
Quanto a sua pergunta, vi o teu arquivo constatei dois problemas no seu projeto VBA:
1. Você escreveu o código público do Módulo1 também num módulo da Plan1. Isso não é bom.
2. Você nomeou o seu projeto VBA com o mesmo nome da função pública VerificaSeFeriado. Não pode. Troque o nome do seu projeto.
Com essas correções a macro funcionará perfeitamente.
Um grande abraço,
Reinaldo
deu certo valeu, fiz as alterações q tu falou e + essas.
Na célula A2, digite a fórmula seguinte e depois copie-a até a linha 32 da mesma coluna.
no lugar dessa forma
=SE(MÊS($A$1+LIN()-LIN($A$1)-1) = MÊS($A$1);$A$1+LIN()-LIN($A$1)-1;””)
alterei deixando assim
=SE(MÊS($K$9)=MÊS($K$9+LIN($K$9)-LIN($K$9));($K$9+LIN($K$9)-LIN($K$9));””)
e no lugar dessa
=SE(A2=””;””;SE(VerificaSeFeriado(A2);”Feriado”;””))
ficou assim
=SE(A13=””;””;SE(MÊS($K$9)=MÊS(A13+LIN(A13)-LIN(A13)+1);(A13+LIN(A13)-LIN(A13)+1);””))
segue o linck para verem como ficou, obrigado pela ajuda, se precisarem podem usar ai.
https://skydrive.live.com/redir.aspx?cid=b1ab94e13a8e512a&resid=B1AB94E13A8E512A!759&parid=B1AB94E13A8E512A!571&authkey=!AIkUlX4OuvTqZKM
gostaria de saber como faço para preencher de cor todos os domingos deste calendário. adorei o site. otimo
Olá Flávio.
Utilizando o mesmo exemplo do artigo, faça assim:
Selecione a tabela (intervalo de B3 a D33) e aplique a formatação condicional com esta fórmula: =DIA.DA.SEMANA($B3;1)=1
Se não souber utilizar a formatação condicional, leia alguns artigos que publiquei sobre o assunto.
https://www.exceldoseujeito.com.br/2009/01/14/aplicando-efeito-zebrado-na-planilha/
https://www.exceldoseujeito.com.br/2008/11/10/realcar-conteudo-duplicado/
https://www.exceldoseujeito.com.br/2009/08/25/formatacao-condicional-estilizada/
Um abraço
Bom dia!
Espero que não esteja desativo, eu gostaria muito de um apoio complementar , preciso criar um linha que mostre a hora mensal trabalhada de segunda a sexta, agradeço se puder me orientar..
Olá Eduardo.
Desculpe, mas não consegui entender sua dúvida.