Logical difficulty

Leandroarb

Board Regular
Joined
Oct 7, 2014
Messages
157
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 7, 2014
Messages
157
Hi Big Joe!
Thanks Oscar too!

This was resolved with:

Code:
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.
 
Last edited:

Leandroarb

Board Regular
Joined
Oct 7, 2014
Messages
157
Hi,
This os the solution:
Code:
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
 

Forum statistics

Threads
1,136,211
Messages
5,674,425
Members
419,508
Latest member
trinstrick

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top