ATENÇÃO NOVOS USUÁRIOS

Se registrou recentemente? Seu cadastro será avaliado e mendiante aprovação, a conta será ativada e você poderá usufruir do fórum. O tempo de avaliação gira em torno de 24 a 48 horas.

Esqueceu sua senha?

Você pode usar o mecanismo de lembrete neste link: Recuperar senha

Você receberá um link de reativação no email cadastrado.

Não recebeu o email? Lembre-se checar o Lixo Eletrônico.

Cálcular dias uteis - DATEDIFF

Discussões sobre a integração do Excel com o Banco de Dados Access

Moderador: joseA

genebaldorios
Colaborador
Colaborador
Mensagens: 16
Registrado em: Qua Jul 19, 2017 10:59 pm

Cálcular dias uteis - DATEDIFF

Mensagem por genebaldorios » Sex Fev 21, 2020 1:11 am

Prezados (as),

No VBA utilizo WorksheetFunction.NetworkDays.INTL(StartDate, EndDate, 7) - 1) e uma tabela de feriados para criar uma coluna calculada dos dias úteis entre datas. Em seguida, utilizo a rotina abaixo para fazer contagem com alguns critérios:

Código: Selecionar todos

Sub ConteSesVBA_Excel()
Dim i               As Long
Dim wsO             As Worksheet: Set wsO = wsDados
Dim wsD             As Worksheet: Set wsD = wsDestino
Dim wkf             As WorksheetFunction
Dim Data_Inicial    As Date
Dim Data_Final      As Date
Dim ultLin          As Long
Dim t               As Single
t = VBA.Timer
Set wkf = Application.WorksheetFunction
ultLin = wsD.Cells(Rows.Count, "A").End(xlUp).Row

Data_Inicial = wsD.Range("C2")
Data_Final = wsD.Range("D2")

For i = 5 To ultLin
    For j = 3 To 11
        wsD.Cells(i, j).Value = wkf.CountIfs(wsO.Range("A2:A" & Rows.Count), wsD.Cells(i, 1), _
        wsO.Range("B2:B" & Rows.Count), wsD.Cells(i, 2), _
        wsO.Range("D2:D" & Rows.Count), wsD.Cells(4, j).Value, _
        wsO.Range("C2:C" & Rows.Count), ">=" & Format(Data_Inicial, "YYYY/MM/DD"), _
        wsO.Range("C2:C" & Rows.Count), "<=" & Format(Data_Final, "YYYY/MM/DD"))
    Next j
Next i    
    Debug.Print VBA.Timer - t
End Sub

A dúvida é possível fazer todos esses cálculos em uma única rotina, porém utilizando instruções de SQL dentro do VBA?
É possível utilizar, por exemplo, DATEDIFF com uma tabela de feriados e excluindo também finais de semana (sábado e domingo):

Código: Selecionar todos

Sub Cacl_Dias_Uteis()
Dim sSQL            As String
Dim dbCon           As New ADODB.Connection
Dim RS              As New ADODB.Recordset
 
    With dbCon
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source= " & ThisWorkbook.FullName & ""
        .Properties("Extended Properties") = "Excel 12.0 XML;HDR=YES"
        .Open
    End With

sSQL = "UPDATE [Vendas$] SET Data_Final = dateDiff('d', #2019/01/01#, #2019/12/31#) WHERE [Vendedor]='Vendedor_1'"
RS.Open sSQL, dbCon

End Sub



Disable adblock

This site is supported by ads and donations.
If you see this text you are blocking our ads.
Please consider a Donation to support the site.


Avatar do usuário
Reinaldo
Jedi
Jedi
Mensagens: 1408
Registrado em: Sex Ago 01, 2014 4:09 pm
Localização: Garça - SP / SCS - SP

Re: Cálcular dias uteis - DATEDIFF

Mensagem por Reinaldo » Sex Fev 21, 2020 11:19 am

Até onde sei, não há no VBA nenhuma função nativa que efetue o que espera/demanda.
DateDiff "fornece/retorna" a diferença entre duas datas em Dias ou meses e/ou ano; se utilizar o parâmetro "w" datediff irá retornar a quantidade do dia referente a primeira data (sem inclui-la),
por exemplo:
Data1= 1/1/2020 (uma quarta-feira) corresponde a um sábado
Data2= 5/2/2020 (tambem quarta) assim DateDiff("w",data1,data2) irá retornar 5,
ou seja são 5 quartas entre as datas, sem considerar a primeira.
Não entendi "...criar uma coluna calculada dos dias úteis entre datas..." pois de acordo com o trecho
"...wsO.Range("C2:C" & Rows.Count), ">=" & Format(Data_Inicial, "YYYY/MM/DD"), _
wsO.Range("C2:C" & Rows.Count), "<=" & Format(Data_Final, "YYYY/MM/DD"))..."
visto que a data inicial e final são em uma única coluna porque não utilizar a função WorksheetFunction.NetworkDays.INTL() diretamente no count


Reinaldo
:oops: :D :mrgreen: :geek:

genebaldorios
Colaborador
Colaborador
Mensagens: 16
Registrado em: Qua Jul 19, 2017 10:59 pm

Re: Cálcular dias uteis - DATEDIFF

Mensagem por genebaldorios » Qua Mar 04, 2020 1:22 am

Reinaldo,

Grato pelo retorno!

To fazendo alguns testes no access e pensei na seguinte possibilidade:

Contar os dias corridos, os feriados e os finais de semana entre duas datas. Após isso, fazer o seguinte cálculo para Dias uteis: DiasCorridos - feriados - sabados/domingos

SELECT a.ID, a.Data_Venda, a.Data_Envio, DateDiff('d',a.Data_Venda,a.Data_Envio) AS Dias_Corridos, (
SELECT Count(*)
FROM Tb_Holidays
WHERE Data_Feriado BETWEEN a.Data_Venda AND a.Data_Envio
) AS DiasUteis
FROM Tb_Dados AS a
SELECT Count(*)
FROM Tb_Calendario ;
WHERE Dia_da_Semana = 1 - Domingo a.Data_Venda OR a.Data_Venda 7 - Sabado
) AS Finais_Semana



Avatar do usuário
Reinaldo
Jedi
Jedi
Mensagens: 1408
Registrado em: Sex Ago 01, 2014 4:09 pm
Localização: Garça - SP / SCS - SP

Re: Cálcular dias uteis - DATEDIFF

Mensagem por Reinaldo » Qua Mar 04, 2020 8:56 am

Sem um modelo/exemplo para poder testar fica dificil,
Pode experimentar (deve ser adaptado a sua realidade) algo tipo:
https://www.tomasvasquez.com.br/forum/v ... coa#p22837
ou
https://www.tomasvasquez.com.br/forum/v ... eis#p14163

Tambem a função exposta em http://www.cpearson.com/excel/betternetworkdays.aspx


Reinaldo
:oops: :D :mrgreen: :geek:

Disable adblock

This site is supported by ads and donations.
If you see this text you are blocking our ads.
Please consider a Donation to support the site.


genebaldorios
Colaborador
Colaborador
Mensagens: 16
Registrado em: Qua Jul 19, 2017 10:59 pm

Re: Cálcular dias uteis - DATEDIFF

Mensagem por genebaldorios » Qua Mar 04, 2020 11:55 pm

Reinaldo,

Segue, em anexo, arquivo com exemplo Calc_DU com arquivo access.
Anexos
Calc_DU.zip
Exemplo_Calc_DU
(45.88 KiB) Baixado 124 vezes



genebaldorios
Colaborador
Colaborador
Mensagens: 16
Registrado em: Qua Jul 19, 2017 10:59 pm

Re: Cálcular dias uteis - DATEDIFF

Mensagem por genebaldorios » Qua Mar 11, 2020 12:44 am

Prezados,
Resolvido. Segue solução:

SELECT a.ID, a.Data_Venda, a.Data_Envio, DateDiff('d',a.Data_Venda,a.Data_Envio)
- (DateDiff ('ww', a.Data_Venda,a.Data_Envio) * 2)
- (IIF(DatePart ('w', a.Data_Venda) = 1, 1 , 0))
- (IIF(DatePart ('w', a.Data_Envio) = 7, 1, 0))
- (SELECT Count(*) FROM Tb_Holidays
WHERE Data_Feriado BETWEEN a.Data_Venda AND a.Data_Envio
AND DatePart('w',Data_Feriado) NOT IN (1,7)
) AS DiasUteis
FROM Tb_Dados AS a;

Reinaldo,

Muito grato pelas dicas!!




Disable adblock

This site is supported by ads and donations.
If you see this text you are blocking our ads.
Please consider a Donation to support the site.


Responder