Date quote sent + 30 Working Days=Date quote valid until

  • Thread starter Thread starter helen simpson
  • Start date Start date
H

helen simpson

Guest
I have a Form which has a text box which the user will input the date that a quote has been sent to a supplier, the next text box needs to be populated automatically with a date which is 30 working days (no weekends) ahead of the sent date. I have looked at various options within this forum, but can only find ones which calculate the number of working days between 2 dates.

Any help would be appreciated, I have a basic understanding of Access, and have only via this problem been looking into modules and expressions etc. Thanks
 
Is suppose it's down to: are you just wanting working days or are you wanting to exclude working days and public holidays?

I can write you the function either way although it depends on what country you are in.
 
Sorry, I was so excited about getting this working, I forgot where I was !! Yes I'm in the UK.
 
Think this should do it:

Copy all of this into a module:

Code:
Public Function AddWorkingDays(ByVal dteStartDate As Date, _
    ByVal intDays As Integer) As Date
    Dim dteTemp As Date
    dteTemp = dteStartDate
    Do While intDays <> 0
        Select Case WeekDay(dteTemp)
            Case Is = 1, 7
            Case Else
                Select Case dteTemp
                    Case Is = DateSerial(Year(dteTemp), 1, 1), _
                        DateOfEaster(Year(dteTemp)) - 2, _
                        DateOfEaster(Year(dteTemp)) + 1, _
                        GetBankHoliday(DateSerial(Year(dteTemp), 5, 1)), _
                        GetBankHoliday(DateSerial(Year(dteTemp), 5, 25)), _
                        GetBankHoliday(DateSerial(Year(dteTemp), 8, 25)), _
                        DateSerial(Year(dteTemp), 12, 25), _
                        DateSerial(Year(dteTemp), 12, 26)
                    Case Else
                        intDays = intDays - 1
                End Select
        End Select
        dteTemp = dteTemp + 1
    Loop
    AddWorkingDays = dteTemp - 1
End Function


Public Function DateOfEaster(ByVal intYear As Integer) As Date
    Dim intDominical As Integer, intEpact As Integer, intQuote As Integer
    intDominical = 225 - (11 * (intYear Mod 19))
    If intDominical > 50 Then
        While intDominical > 50
            intDominical = intDominical - 30
        Wend
    End If
    If intDominical > 48 Then intDominical = intDominical - 1
    intEpact = (intYear + Int(intYear / 4) + intDominical + 1) Mod 7
    intQuote = intDominical + 7 - intEpact
    If intQuote > 31 Then
        DateOfEaster = DateSerial(intYear, 4, intQuote - 31)
    Else
        DateOfEaster = DateSerial(intYear, 3, intQuote)
    End If
End Function

Public Function GetBankHoliday(ByRef dteBankHoliday As Date) As Date
    Dim intCounter As Integer
    For intCounter = 0 To 6
        If WeekDay(dteBankHoliday + intCounter) = 2 Then
            dteBankHoliday = dteBankHoliday + intCounter
            Exit For
        End If
    Next intCounter
    GetBankHoliday = dteBankHoliday
End Function



To test it out call it with: AddWorkingDays(a date, number of days)
 
Thank you very much for all your help, I will gives this a go.
 

Users who are viewing this thread

Back
Top Bottom