Link to home
Create AccountLog in
Avatar of mustish1
mustish1

asked on

Ending day is wrong

Can you please help me with the script? The course ending day is wrong.


----------------------------------------------

Course start day   Course end day

01/23/2023      03/02/2023

03/07/2023              04/13/2023

----------------------------------------------


Private Sub Command10_Click()

Dim startDate As Date

Dim courseType As String

Dim endDate As Date


startDate = #03/07/2023#

courseType = "IPC"


endDate = CalculateEndDate(startDate, courseType)


MsgBox "The end date is: " & endDate


End Sub


Function CalculateEndDate(startDate As Date, courseType As String) As Date

    Dim holidays(0 To 62) As Date

    holidays(0) = #1/1/2021#

    holidays(1) = #1/2/2021#

    holidays(2) = #1/18/2021#

    holidays(3) = #2/15/2021#

    holidays(4) = #5/31/2021#

    holidays(5) = #10/11/2021#

    holidays(6) = #11/11/2021#

    holidays(7) = #11/25/2021#

    holidays(8) = #12/19/2021#

    holidays(9) = #12/20/2021#

    holidays(10) = #12/21/2021#

    holidays(11) = #12/23/2021#

    holidays(12) = #12/24/2021#

    holidays(13) = #12/25/2021#

    holidays(14) = #12/26/2021#

    holidays(15) = #12/27/2021#

    holidays(16) = #12/28/2021#

    holidays(17) = #12/29/2021#

    holidays(18) = #12/30/2021#

    holidays(19) = #10/31/2021#

    holidays(20) = #1/1/2022#

    holidays(21) = #1/2/2022#

    holidays(22) = #1/17/2022#

    holidays(23) = #2/21/2022#

    holidays(24) = #5/30/2022#

    holidays(25) = #7/4/2022#

    holidays(26) = #9/5/2022#

    holidays(27) = #10/10/2022#

    holidays(28) = #11/11/2022#

    holidays(29) = #11/24/2022#

    holidays(30) = #12/20/2022#

    holidays(31) = #12/21/2022#

    holidays(32) = #12/22/2022#

    holidays(33) = #12/23/2022#

    holidays(34) = #12/24/2022#

    holidays(35) = #12/25/2022#

    holidays(36) = #12/26/2022#

    holidays(37) = #12/27/2022#

    holidays(38) = #12/28/2022#

    holidays(39) = #12/29/2022#

    holidays(40) = #12/30/2022#

    holidays(41) = #12/31/2022#

    holidays(42) = #1/1/2023#

    holidays(43) = #1/2/2023#

    holidays(44) = #1/16/2023#

    holidays(45) = #2/20/2023#

    holidays(46) = #5/29/2023#

    holidays(47) = #7/4/2023#

    holidays(48) = #9/4/2023#

    holidays(49) = #10/9/2023#

    holidays(50) = #11/10/2023#

    holidays(51) = #11/23/2023#

    holidays(52) = #12/23/2023#

    holidays(53) = #12/24/2023#

    holidays(54) = #12/25/2023#

    holidays(55) = #12/26/2023#

    holidays(56) = #12/27/2023#

    holidays(57) = #12/28/2023#

    holidays(58) = #12/29/2023#

    holidays(59) = #12/30/2023#

    holidays(60) = #12/31/2023#

    holidays(61) = #1/1/2024#

    holidays(62) = #1/2/2024#


    Dim totalDays As Integer

    Dim endDate As Date

    Dim i As Integer

    

    ' Set total days based on course type

    If courseType = "IPC" Then

        totalDays = 28

    ElseIf courseType = "AQC" Then

        totalDays = 54

    Else

        ' Invalid course type

        Exit Function

    End If

    

    ' Add total days to start date

    endDate = DateAdd("d", totalDays, startDate)

    

    ' Exclude weekends and company vacation days

    For i = 0 To 62

        If Weekday(endDate) = vbSaturday Then

            endDate = DateAdd("d", 2, endDate)

        ElseIf Weekday(endDate) = vbSunday Then

            endDate = DateAdd("d", 1, endDate)

        ElseIf endDate = holidays(i) Then

            endDate = DateAdd("d", 1, endDate)

        End If

    Next i

    

    CalculateEndDate = endDate

End Function


Avatar of Partha Mandayam
Partha Mandayam
Flag of India image

What is wrong in the ending day
What are you getting and what should it be
Avatar of mustish1
mustish1

ASKER

it should be 04/13/2023
ASKER CERTIFIED SOLUTION
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account