Calculating Holidays

F

Fay

My form contains these fields:

Number (Autonumber)
FirstName (Text)
StartDate (Date)
EndDate (Date)
Holidays (Number)

I want to calculate the number of days inbetween and including StartDate and
EndDate—excluding any Saturdays and Sundays.

This is my standard module.

Public Sub CalculateNoHolidays()

On Error GoTo Err_Holidays

Dim intCount As Integer
Dim Holidays As Integer

'StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
Holidays = intCount

Exit_Holidays:
Exit Sub

Err_Holidays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_Holidays
End Select

This is the code I created for the EndDate field in AfterUpDate. It is
supposed to call up the standard procedure.

Private Sub EndDate_AfterUpdate()
CalculateNoHolidays
End Sub

I set a breakpoint at Private Sub EndDate_AfterUpdate() to test the code.

What happens is that when it switches over to the standard module, it looks
at the first date—goes back to the Do While StartDate <= EndDate and then
goes out of the loop to Holidays = intCount Holidays show as a zero.

What is wrong?

Please be patient—I am just starting to learn VBA—I am a senior—don’t use
big technical words. Thank you.
 
K

Klatuu

Fay,

Here is a version of what you are trying to do. Rather than have to user
have to determine the number of holidays and enter them, create a holiday
table. All you need is the date of the holiday, but for convenience, mine
has a description of the holiday. Now all you need to enter is the start
date and end date, and this function will return the number of "working days"
between the two dates.

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, DtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, DtmEnd) - _
(DateDiff("ww", dtmStart, DtmEnd, vbSaturday) + _
DateDiff("ww", dtmStart, DtmEnd, vbSunday)) + 1

'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", _
"[HOLI_DATE] between #" & dtmStart & "# And #" & DtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function
 
F

Fay

Klatuu
I guess I didn't make myself clear enough--I want to be able to type in the
first day of my annual holiday [StartDate] and the last day of my annual
holiday [EndDate]. Then I want VB is look through these dates and the ones
inbetween and count the number of days not including Saturday and Sunday so
that I know exactly how many days of my annual holiday I have used.

I read through your sample--but I really don't know how to change it for my
situation.

Would I still set your sample up as a standard module and then call it in
from the update event of the EndDate.

Thank you for trying to help me.

Fay

Klatuu said:
Fay,

Here is a version of what you are trying to do. Rather than have to user
have to determine the number of holidays and enter them, create a holiday
table. All you need is the date of the holiday, but for convenience, mine
has a description of the holiday. Now all you need to enter is the start
date and end date, and this function will return the number of "working days"
between the two dates.

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, DtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, DtmEnd) - _
(DateDiff("ww", dtmStart, DtmEnd, vbSaturday) + _
DateDiff("ww", dtmStart, DtmEnd, vbSunday)) + 1

'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", _
"[HOLI_DATE] between #" & dtmStart & "# And #" & DtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


Fay said:
My form contains these fields:

Number (Autonumber)
FirstName (Text)
StartDate (Date)
EndDate (Date)
Holidays (Number)

I want to calculate the number of days inbetween and including StartDate and
EndDate—excluding any Saturdays and Sundays.

This is my standard module.

Public Sub CalculateNoHolidays()

On Error GoTo Err_Holidays

Dim intCount As Integer
Dim Holidays As Integer

'StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
Holidays = intCount

Exit_Holidays:
Exit Sub

Err_Holidays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_Holidays
End Select

This is the code I created for the EndDate field in AfterUpDate. It is
supposed to call up the standard procedure.

Private Sub EndDate_AfterUpdate()
CalculateNoHolidays
End Sub

I set a breakpoint at Private Sub EndDate_AfterUpdate() to test the code.

What happens is that when it switches over to the standard module, it looks
at the first date—goes back to the Do While StartDate <= EndDate and then
goes out of the loop to Holidays = intCount Holidays show as a zero.

What is wrong?

Please be patient—I am just starting to learn VBA—I am a senior—don’t use
big technical words. Thank you.
Click to expand...
Click to expand...
 
F

Fay

Chris
Thanks for the link.

In the form I am trying to develop, I want to be able to enter the
[StartDate] of my annual holidays and the [EndDate] of my holidays--then have
VB count the number of days including the StartDate and the
EndDate--excluding Saturday and Sunday--so that I can be sure that I am using
the correct number of my annual holidays.

Could you give me a simple code for that?

Fay
 
F

Fay

Klatuu

My form has the following fields:

Number (Autonumber)
FirstName (Text)
dtmStart (Date)
DtmEnd (Date)
WorkDays (Number)

I want to determine the number of work days inbetween and including dtmStart
and DtmEnd—excluding Saturday and Sunday. At the moment I am not interested
in removing statutory holidays.

This is my standard module:

Public Function CalcWorkDays(dtmStart As Date, DtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, DtmEnd) - _
(DateDiff("ww", dtmStart, DtmEnd, vbSaturday) + _
DateDiff("ww", dtmStart, DtmEnd, vbSunday)) + 1

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function


This is my module attached toAfterUpDate in the WorkDays field in my form:

Private Sub DtmEnd_AfterUpdate()
CalcWorkDays
End Sub

When I set a breakpoint at Private Sub DtmEnd_AfterUpdate(), it moves to
CalcWorkDays and then I get the following error:

Compile Error
Argument not optional

What am I doing incorrectly?
Fay

Klatuu said:
Fay,

Here is a version of what you are trying to do. Rather than have to user
have to determine the number of holidays and enter them, create a holiday
table. All you need is the date of the holiday, but for convenience, mine
has a description of the holiday. Now all you need to enter is the start
date and end date, and this function will return the number of "working days"
between the two dates.

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, DtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, DtmEnd) - _
(DateDiff("ww", dtmStart, DtmEnd, vbSaturday) + _
DateDiff("ww", dtmStart, DtmEnd, vbSunday)) + 1

'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", _
"[HOLI_DATE] between #" & dtmStart & "# And #" & DtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


Fay said:
My form contains these fields:

Number (Autonumber)
FirstName (Text)
StartDate (Date)
EndDate (Date)
Holidays (Number)

I want to calculate the number of days inbetween and including StartDate and
EndDate—excluding any Saturdays and Sundays.

This is my standard module.

Public Sub CalculateNoHolidays()

On Error GoTo Err_Holidays

Dim intCount As Integer
Dim Holidays As Integer

'StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
Holidays = intCount

Exit_Holidays:
Exit Sub

Err_Holidays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_Holidays
End Select

This is the code I created for the EndDate field in AfterUpDate. It is
supposed to call up the standard procedure.

Private Sub EndDate_AfterUpdate()
CalculateNoHolidays
End Sub

I set a breakpoint at Private Sub EndDate_AfterUpdate() to test the code.

What happens is that when it switches over to the standard module, it looks
at the first date—goes back to the Do While StartDate <= EndDate and then
goes out of the loop to Holidays = intCount Holidays show as a zero.

What is wrong?

Please be patient—I am just starting to learn VBA—I am a senior—don’t use
big technical words. Thank you.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Holiday day tracking 2
Calculate businness Days 1
Holiday - Error 4
Working Day calculations 1
Problems with MS Access reading dates as US, not British formats. 2
DateDiff Working Days only not including holidays code problem 7
DateDiff Working Days only not including holidays code problem 1
#Error in Results 5

Top