Logical difficulty

[Leandroarb]

Hi,

I need to understand a logic.


I have services that have a lead time that can be from 1 to 30 days.
as of the date that this service was issued, I need to add to its date the days of its execution period. I can not add Saturdays, Sundays and holidays.
I have a table with all the days that I can not add.
I need to get the date of issue, add another day, and look at the table of dates not countable, if it is located in the table, I can not consider that day, so I have to add two more days and look again. This should be repeated, increasing the number of days until the execution time is the date of issue plus the term in working days.

Example:


If the service is issued on 04/13/2018 and the execution time is 7 days, your deadline will be: 04/24/2018 according to the calendar of my city.


All help will be appreciated

 

[Oscar Cronquist]

The WORKDAY function allows you to add working days to a date and use a list of holidays.
https://support.office.com/en-us/article/workday-function-f764a5b7-05fc-4494-9486-60d494efbf33

 

[Joe4]

The WORKDAY function allows you to add working days to a date and use a list of holidays.
https://support.office.com/en-us/art...6-60d494efbf33

That is an Excel solution.

Note that this question is located in the "Microsoft Questions" forum, not the "Excel Questions" one.
If you are using the "Zero Reply Posts" listing in locating unanswered questions, be sure to look over in the right-hand column to see which forum the question is listed in, as that listing lists unanswered questions across multiple forums (not just the "Excel Questions" one).

Regarding the question at hand, some people used VBA to create their own WorkDay function for Access.
See: http://access.mvps.org/access/datetime/date0012.htm

 

[Leandroarb]

Hi Big Joe!
Thanks Oscar too!

This was resolved with:

Public Function PrazoFinal(iLocal As Integer, dataP As Date, sPrazo As String) As String
On Error Resume Next
Dim j As Byte
Dim cont As Integer
Dim dtP As Date


intLocal = iLocal
cont = 0


Do While cont < Int(Left(sPrazo, 2)) '30
    j = j + 1
    dtCrit = Format(DateAdd("d", j, dataP), "dd/mm/yyyy")
    If Len(fncVerif & dtCrit) = 10 Then
        cont = cont + 1
    End If
Loop
PrazoFinal = dtCrit
End Function


Function fncVerif()
On Error Resume Next
Dim dbVerif As DAO.Database
Dim rsVerif As DAO.Recordset
Dim strVerif As String


strVerif = "SELECT TabDatasAbono.[" & intLocal & "] As Check" & _
           " FROM TabDatasAbono" & _
           " WHERE [" & intLocal & "]=#" & dtCrit & "#"


Set dbVerif = CurrentDb()
Set rsVerif = dbVerif.OpenRecordset(strVerif)


fncVerif = Format(rsVerif!Check, "mm/dd/yyyy")


Set dbVerif = Nothing
dbVerif.Close
Set rsVerif = Nothing
rsVerif.Close
End Function

I posted in the access room, but forgot to report that it was for this tool. I apologize and thank to all.

 

[Oscar Cronquist]

I made a mistake, I am sorry.

 

[Leandroarb]

Not existis mistake in a try help.
I thank.

 

[Leandroarb]

Hi,
This os the solution:

Public Function PrazoFinal(iLocal As Integer, dataP As Date, sPrazo As String)
On Error Resume Next
Dim j      As Integer
Dim cont   As Integer
Dim d      As Integer
Dim strHora As String

intLocal = iLocal
cont = 0
j = 0

Do
    If Right(sPrazo, 1) = "h" Then 'Se o prazo do serviços for em horas
        PrazoFinal = DateAdd("h", 4, dataP) 'Adiciona 4 horas à Data e Hora do protocolo
        Exit Function 'Sai da função
    End If
    
    j = j + 1 'Adiciona mais um ao contador para acréscimo de dias na data e hora do protocolo
    strHora = Format(dataP, "hh:mm:ss") 'Pega a hora, minuto e segundo do protocolo
    dtCrit = Format(DateAdd("d", j, dataP), "dd/mm/yyyy") 'Cria um critério para a função fncVerif com a data do protocolo + 1 dia
    
    lngData = CLng(dtCrit) 'Tranforma a data em um número longo
        If fncVerif = 0 Then 'Se a função retornar Zero, quer dizer que encontrou um dia útil
            PrazoFinal = dtCrit & " " & strHora 'Adiciona esse dia ao prazo final
            cont = cont + 1 'Adiciona mais 1 ao contador de comparação do Loop While
        End If
Loop While cont < Int(Left(sPrazo, 2)) 'Enquanto o contador for menos que o prazo em dias do serviço, ele continuará repetindo

End Function

Function fncVerif() As Long
On Error Resume Next
Dim dbVerif  As DAO.Database
Dim rsVerif As DAO.Recordset
Dim strVerif       As String

fncVerif = 0

strVerif = "SELECT CLng([" & intLocal & "]) AS DataLng" & _
           " FROM TabDatasAbono" & _
           " WHERE CLng([" & intLocal & "])=" & lngData 'lngData é variável Global

Set dbVerif = CurrentDb()
Set rsVerif = dbVerif.OpenRecordset(strVerif)

fncVerif = rsVerif!DataLng


Set dbVerif = Nothing
dbVerif.Close
Set rsVerif = Nothing
rsVerif.Close
End Function

Thanks