Link to home
Create AccountLog in
Avatar of mustish1
mustish1

asked on

Calculation was wrong

Can you please help me with this function? It's not calculating the right value. I am using Microsoft Access, 2016

User generated image


Course_Start_Day= "03/07/2023"
Course_Duration=28 'days
Course_ending_day=

03/07/2023 'Day 1
03/08/2023 'Day 2
03/09/2023 'Day 3
03/10/2023 'Day 4
03/13/2023 'Day 5
03/14/2023 'Day 6
03/15/2023 'Day 7
03/16/2023 'Day 8
03/17/2023 'Day 9
03/20/2023 'Day 10
03/21/2023 'Day 11
03/22/2023 'Day 12
03/23/2023 'Day 13
03/24/2023 'Day 14
03/27/2023 'Day 15
03/28/2023 'Day 16
03/29/2023 'Day 17
03/30/2023 'Day 18
03/31/2023 'Day 19
04/03/2023 'Day 20
04/04/2023 'Day 21
04/05/2023 'Day 22
04/06/2023 'Day 23
04/07/2023 'Day 24
04/10/2023 'Day 25
04/11/2023 'Day 26
04/12/2023 'Day 27
04/13/2023 'Day 28
04/14/2023 'Day 29
User generated image

Private Sub Command10_Click()

Dim Course_Start_Day As Date
Dim Course_Duration As Integer
Dim holidays(0 To 194) As Date
Dim Course_Ending_Day As Date

holidays(0) = #10/9/2017# 'Columbus Day
holidays(1) = #11/10/2017# 'Veterans Day
holidays(2) = #11/23/2017# 'Thanksgiving Day
holidays(3) = #11/24/2017# 'No activity
holidays(4) = #12/20/2017# 'Holiday
holidays(5) = #12/21/2017# 'Holiday
holidays(6) = #12/21/2017# 'Holiday
holidays(7) = #12/22/2017# 'Holiday
holidays(8) = #12/23/2017# 'Holiday
holidays(9) = #12/24/2017# 'Holiday
holidays(10) = #12/25/2017# 'Holiday
holidays(11) = #12/26/2017# 'Holiday
holidays(12) = #12/27/2017# 'Holiday
holidays(13) = #12/28/2017# 'Holiday
holidays(14) = #12/29/2017# 'Holiday
holidays(15) = #12/30/2017# 'Holiday
holidays(16) = #12/31/2017# 'Holiday
holidays(17) = #1/1/2018# 'Holiday
holidays(18) = #1/2/2018# 'Holiday
holidays(19) = #1/15/2018# 'Martin Luther King Day
holidays(20) = #2/19/2018# 'Presidents Day
holidays(21) = #5/28/2018# 'Memorial Day
holidays(22) = #7/4/2018# 'Independence Day
holidays(23) = #7/5/2018# 'No activity
holidays(24) = #8/10/2018# 'Columbus Day
holidays(25) = #8/31/2018# 'No activity
holidays(26) = #9/3/2018# 'Labor Day
holidays(27) = #11/12/2018# 'Veterans Day
holidays(28) = #11/22/2018# 'Thanksgiving Day
holidays(29) = #11/23/2018# 'No activity
holidays(30) = #12/20/2018# 'Holiday
holidays(31) = #12/21/2018# 'Holiday
holidays(32) = #12/22/2018# 'Holiday
holidays(33) = #12/23/2018# 'Holiday
holidays(34) = #12/24/2018# 'Holiday
holidays(35) = #12/25/2018# 'Holiday
holidays(36) = #12/26/2018# 'Holiday
holidays(37) = #12/27/2018# 'Holiday
holidays(38) = #12/28/2018# 'Holiday
holidays(39) = #12/29/2018# 'Holiday
holidays(40) = #12/30/2018# 'Holiday
holidays(41) = #12/31/2018# 'Holiday
holidays(42) = #1/1/2019# 'Holiday
holidays(43) = #1/2/2019# 'Holiday
holidays(44) = #1/21/2019# 'Martin Luther King Day
holidays(45) = #2/18/2019# 'Presidents Day
holidays(46) = #5/27/2023# 'Memorial Day
holidays(47) = #7/4/2019# 'Independence Day
holidays(48) = #7/5/2019# 'No activity
holidays(49) = #8/30/2019# 'No activity
holidays(50) = #9/2/2019# 'Labor Day
holidays(51) = #10/14/2019# 'Columbus Day
holidays(52) = #11/11/2019# 'Veterans Day
holidays(53) = #11/28/2019# 'Thanksgiving Day
holidays(54) = #11/29/2019# 'No activity
holidays(55) = #12/20/2019# 'Holiday
holidays(56) = #12/21/2019# 'Holiday
holidays(57) = #12/22/2019# 'Holiday
holidays(58) = #12/23/2019# 'Holiday
holidays(59) = #12/24/2019# 'Holiday
holidays(60) = #12/25/2019# 'Holiday
holidays(61) = #12/26/2019# 'Holiday
holidays(62) = #12/27/2019# 'Holiday
holidays(63) = #12/28/2019# 'Holiday
holidays(64) = #12/29/2019# 'Holiday
holidays(65) = #12/30/2019# 'Holiday
holidays(66) = #12/31/2019# 'Holiday
holidays(67) = #1/1/2020# 'Holiday
holidays(68) = #1/2/2020# 'Holiday
holidays(69) = #1/3/2020# 'Holiday
holidays(70) = #1/20/2020# 'Martin Luther King's Day
holidays(71) = #2/17/2020# 'President''s Day
holidays(72) = #3/7/2020# 'Independence Day
holidays(73) = #5/5/2020# 'Memorial Day
holidays(74) = #5/25/2020# 'Memorial Day
holidays(75) = #7/2/2020# 'No activity
holidays(76) = #7/3/2020# 'Independence Day
holidays(77) = #9/4/2020# 'No activity
holidays(78) = #9/7/2020# 'Labor Day
holidays(79) = #10/12/2020# 'Columbus Day
holidays(80) = #11/11/2020# 'Veterans Day
holidays(81) = #11/26/2020# 'Thanksgiving Day
holidays(82) = #12/19/2020# 'Holiday
holidays(83) = #12/20/2020# 'Holiday
holidays(84) = #12/21/2020# 'Holiday
holidays(85) = #12/22/2020# 'Holiday
holidays(86) = #12/23/2020# 'Holiday
holidays(87) = #12/24/2020# 'Holiday
holidays(88) = #12/25/2020# 'Holiday
holidays(89) = #12/26/2020# 'Holiday
holidays(90) = #12/27/2020# 'Holiday
holidays(91) = #12/28/2020# 'Holiday
holidays(92) = #12/29/2020# 'Holiday
holidays(93) = #12/30/2020# 'Holiday
holidays(94) = #12/31/2020# 'Holiday
holidays(95) = #1/1/2021# 'Holiday
holidays(96) = #1/2/2021# 'Holiday
holidays(97) = #1/18/2021# 'Martin Luther King's Day
holidays(98) = #2/15/2021# 'President's Day
holidays(99) = #5/31/2021# 'Memorial Day
holidays(100) = #7/5/2021# 'Independence Day
holidays(101) = #9/6/2021# 'Labor Day
holidays(102) = #10/11/2021# 'Columbus Day
holidays(103) = #11/11/2021# 'Veterans Day
holidays(104) = #11/25/2021# 'Thanksgiving Day
holidays(105) = #12/18/2021# 'Holiday
holidays(106) = #12/19/2021# 'Holiday
holidays(107) = #12/20/2021# 'Holiday
holidays(108) = #12/21/2021# 'Holiday
holidays(109) = #12/22/2021# 'Holiday
holidays(110) = #12/23/2021# 'Holiday
holidays(111) = #12/24/2021# 'Holiday
holidays(112) = #12/25/2021# 'Holiday
holidays(113) = #12/26/2021# 'Holiday
holidays(114) = #12/27/2021# 'Holiday
holidays(115) = #12/28/2021# 'Holiday
holidays(116) = #12/29/2021# 'Holiday
holidays(117) = #12/30/2021# 'Holiday
holidays(118) = #12/31/2021# 'Holiday
holidays(119) = #1/1/2022# 'Holiday
holidays(120) = #1/1/2022# 'Holiday
holidays(121) = #1/2/2022# 'Holiday
holidays(122) = #1/17/2022# 'Martin Luther King's Day
holidays(123) = #2/21/2022# 'President's Day
holidays(124) = #5/30/2022# 'Holiday
holidays(125) = #7/4/2022# 'Independence Day
holidays(126) = #9/5/2022# 'Labor Day
holidays(127) = #10/10/2022# 'Columbus Day
holidays(128) = #11/11/2022# 'Veterans Day
holidays(129) = #11/24/2022# 'Thanksgiving Day
holidays(130) = #12/20/2022# 'Holiday
holidays(131) = #12/21/2022# 'Holiday
holidays(132) = #12/22/2022# 'Holiday
holidays(133) = #12/23/2022# 'Holiday
holidays(134) = #12/24/2022# 'Holiday
holidays(135) = #12/25/2022# 'Holiday
holidays(136) = #12/26/2022# 'Holiday
holidays(137) = #12/27/2022# 'Holiday
holidays(138) = #12/28/2022# 'Holiday
holidays(139) = #12/29/2022# 'Holiday
holidays(140) = #12/30/2022# 'Holiday
holidays(141) = #12/31/2022# 'Holiday
holidays(142) = #1/1/2023# 'Holiday
holidays(143) = #1/2/2023# 'Holiday
holidays(144) = #1/16/2023# 'Martin Luther King's Day
holidays(145) = #2/20/2023# 'President's Day
holidays(146) = #5/29/2023# 'Memorial Day
holidays(147) = #7/4/2023# 'Independence Day
holidays(148) = #9/4/2023# 'Labor Day
holidays(149) = #10/9/2023# 'Columbus Day
holidays(150) = #11/10/2023# 'Veterans Day
holidays(151) = #11/23/2023# 'Holiday
holidays(152) = #12/20/2023# 'Holiday
holidays(153) = #12/21/2023# 'Holiday
holidays(154) = #12/22/2023# 'Holiday
holidays(155) = #12/23/2023# 'Holiday
holidays(156) = #12/24/2023# 'Holiday
holidays(157) = #12/25/2023# 'Holiday
holidays(158) = #12/26/2023# 'Holiday
holidays(159) = #12/27/2023# 'Holiday
holidays(160) = #12/28/2023# 'Holiday
holidays(161) = #12/29/2023# 'Holiday
holidays(162) = #12/30/2023# 'Holiday
holidays(163) = #12/31/2023# 'Holiday
holidays(164) = #1/1/2024# 'Holiday
holidays(165) = #1/2/2024# 'Holiday
holidays(166) = #1/2/2024# 'Holiday
holidays(167) = #1/15/2024# 'Martin Luther King's Day
holidays(168) = #1/30/2024# 'Holiday
holidays(169) = #2/19/2024# 'President's Day
holidays(170) = #5/27/2024# 'Memorial Day
holidays(171) = #6/19/2024# 'Juneteenth
holidays(172) = #7/4/2024# 'Independence Day
holidays(173) = #9/2/2024# 'Labor Day
holidays(174) = #10/14/2024# 'Columbus Day
holidays(175) = #11/11/2024# 'Veteran's Day
holidays(176) = #11/28/2024# 'Thanksgiving Day
holidays(177) = #12/20/2024# 'Holiday
holidays(178) = #12/21/2024# 'Holiday
holidays(179) = #12/22/2024# 'Holiday
holidays(180) = #12/23/2024# 'Holiday
holidays(181) = #12/24/2024# 'Holiday
holidays(182) = #12/25/2024# 'Holiday
holidays(183) = #12/26/2024# 'Holiday
holidays(184) = #12/27/2024# 'Holiday
holidays(185) = #12/28/2024# 'Holiday
holidays(186) = #12/29/2024# 'Holiday
holidays(187) = #12/30/2024# 'Holiday
holidays(188) = #12/31/2024# 'Holiday
holidays(189) = #1/1/2025# 'Holiday
holidays(190) = #1/2/2025# 'Holiday
holidays(191) = #1/20/2025# 'Martin Luther King's Day
holidays(192) = #2/17/2025# 'President's Day
holidays(193) = #5/26/2025# 'Memorial Day
holidays(194) = #6/19/2025# 'Juneteenth

Course_Start_Day = #3/7/2023#
Course_Duration = 28
Course_Ending_Day = GetCourseEndingDay(Course_Start_Day, Course_Duration, holidays)

MsgBox "The course ending day is " & Course_Ending_Day

End Sub

Function GetCourseEndingDay(Course_Start_Day As Date, Course_Duration As Integer, holidays() As Date) As Date
Dim i As Integer
Dim count As Integer
Dim end_date As Date

end_date = Course_Start_Day
count = 0

Do While count < Course_Duration
end_date = end_date + 1 'add one day to the end_date

'check if the end_date is a weekend (Saturday or Sunday)
If Weekday(end_date, vbSaturday) = 1 Or Weekday(end_date, vbSaturday) = 7 Then
'subtract one day to offset the added day
end_date = end_date - 1
Else
'check if the end_date is a holiday
For i = 0 To UBound(holidays)
If end_date = holidays(i) Then
'subtract one day to offset the added day
end_date = end_date - 1
Exit For
End If
Next i
End If

count = count + 1
Loop

GetCourseEndingDay = end_date
End Function


ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

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