Vídeo recomendado
https://youtu.be/diWPPPhW-9E

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 »

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: 1537
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 »

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


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

Re: Cálcular dias uteis - DATEDIFF

Mensagem por genebaldorios »

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: 1537
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 »

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


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 »

Reinaldo,

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


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

Re: Cálcular dias uteis - DATEDIFF

Mensagem por genebaldorios »

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!!


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

Re: Cálcular dias uteis - DATEDIFF [RESOLVIDO]

Mensagem por genebaldorios »



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