Attend Excelapalooza
Thanks Thanks:  0
Results 1 to 7 of 7

Thread: Logical difficulty

  1. #1
    Board Regular
    Join Date
    Oct 2014
    Location
    Apucarana estate of Paraná, Brazil.
    Posts
    84
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Logical difficulty

    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
    |L|E|A|N|D|R|O| |B|A|R|B|O|S|A|
    -------------------------------
    |S|O|L|I| |D|E|O| |G|L|O|R|I|A|
    -------------------------------

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  2. #2
    Board Regular
    Join Date
    Apr 2018
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Logical difficulty

    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

  3. #3
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    44,835
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Logical difficulty

    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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Board Regular
    Join Date
    Oct 2014
    Location
    Apucarana estate of Paraná, Brazil.
    Posts
    84
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Logical difficulty

    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 by Leandroarb; May 14th, 2018 at 03:16 PM.
    |L|E|A|N|D|R|O| |B|A|R|B|O|S|A|
    -------------------------------
    |S|O|L|I| |D|E|O| |G|L|O|R|I|A|
    -------------------------------

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  5. #5
    Board Regular
    Join Date
    Apr 2018
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Logical difficulty

    I made a mistake, I am sorry.

  6. #6
    Board Regular
    Join Date
    Oct 2014
    Location
    Apucarana estate of Paraná, Brazil.
    Posts
    84
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Logical difficulty

    Not existis mistake in a try help.
    I thank.
    |L|E|A|N|D|R|O| |B|A|R|B|O|S|A|
    -------------------------------
    |S|O|L|I| |D|E|O| |G|L|O|R|I|A|
    -------------------------------

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  7. #7
    Board Regular
    Join Date
    Oct 2014
    Location
    Apucarana estate of Paraná, Brazil.
    Posts
    84
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Logical difficulty

    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com