Difference bewteen two Date Fields minus Weekends

  • Thread starter Thread starter Stockwell43
  • Start date Start date
S

Stockwell43

Hello,

I have a txtDateFrom1 and a ELPRD(another date field).

When the txtDatefrom1 has a date and the ELPRD has a later day, I want to
click a button to calculate the number of days minus the weekends. I don't
need holidays, they can just manual minus one day out when it happens. I want
to try and keep the code as simple as possible without getting into queries
and more tables. If possible, please simplify answer.

Thanks!!
 
Hi Doug, thank you for responding.

The first piece is what I would want but how do I go about using it? In
other words, Do I copy it into a Module and that's it or do I need to place
it in an event procedure in one of my field or a command button? If you could
walk my through it, I would greatly appreciate it.

Thanks!!!
 
Copy what's between Code Start and Code End into a new module (not a class
module nor a module associated with a form or report). Make sure that the
name of the module is not the same as the functions within it (in other
words, don't name the module WorkingDays or WorkingDays2).

Call the function as WorkingDays(FirstDate, SecondDate).

(Sorry, but since I don't know exactly what you want to do with it, I can't
be more specific)
 
I created a Module named Module33 and took off the Start Code line and End
Code line.

I placed three fields on my form. One named StartDate with Startdate as the
control source(because I want to save the date to the table) another field
named EndDate with control source as EndDate(because I want to save the date
to the table) and a third field to show the number of working days between
the start date and end date.

So, I want the user to enter a start date, then enter an end date and when
they exit out of the end date field, I want my third field (LC Wait Time,
named LCWT) to show the acutual number of working days between the two dates
without weekends.

Does that make make sense? The third field LC Wait Time is control source
LCWT because I want to save that to the table as well for reporting purposes.
 
Try replacing the existing WorkingDays function with:

Public Function WorkingDays(StartDate As Variant, EndDate As Variant) As
Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
' Modified by Doug Steele to handle non-date input
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

If IsNull(StartDate) Or IsNull(EndDate) Then
WorkingDays = 0
Else
If IsDate(StartDate) And IsDate(EndDate) Then
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
WorkingDays = intCount
Else
WorkingDays = 0
End If
End If

Exit_WorkingDays:
Exit Function

You can then set the ControlSource of that third field to

=WorkingDays(StartDate, EndDate)

You shouldn't be storing the calculated value in the table. If you need it
elsewhere, create a query that adds the function as a calculated column, and
use the query rather than the table.
 
Hi Doug,

When I paste what you sent in a new module, the very first line Public
Function Workdays etc... shows in red. Did I do something wrong or should I
not have placed it in a module?

Thanks!
 
Likely word-wrap. It's supposed to be a single line starting with Public and
ending with Integer.

You could also use

Public Function WorkingDays( _
StartDate As Variant, _
EndDate As Variant _
) As Integer

(the underscores are line continuation characters, so that those 4 lines of
text actually equate to a single line of code)
 
I placed in the code you gave me in a Module and named the Module, Module33.
In the third field I placed the code you gave me in the Control Source and in
the Name field I called it WorkingDays. When I click the form out of Design
View it takes me to the Module and an error message pops up that says:
Complie Error: Label not defined and when I click OK it hightlights the in
yellow the part you correct with the underscores. I must be doing something
wrong because your instructions were quite simple. Was I suppose to put
something in an event procedure or in the forms code?

Thanks!!
 
In the third field you said to put in =WorkingDays(StartDate, EndDate) in the
control source. Was this not correct?
 
Assuming the names of the fields are StartDate and EndDate, yes, that's
correct.

Let's back up again. Exactly what's in your module? (Copy-and-paste into
your response, just in case you put something that shouldn't be there, or
forgot to remove something)
 
Yes the two date fields are names StartDate and EndDate. Thanks Doug!


Public Function WorkingDays( _
StartDate As Variant, _
EndDate As Variant _
) As Integer

'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
' Modified by Doug Steele to handle non-date input
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

If IsNull(StartDate) Or IsNull(EndDate) Then
WorkingDays = 0
Else
If IsDate(StartDate) And IsDate(EndDate) Then
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
WorkingDays = intCount
Else
WorkingDays = 0
End If
End If

Exit_WorkingDays:
Exit Function

End Function
 
Sorry about that: looking back at my original code, I see that some of the
copy-and-paste stuff didn't make it.

There's code missing between

Exit_WorkingDays:
Exit Function

and

End Function

it should actually be

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
 
Hi Doug,

Thank you, the code works great!!!! You've always been a big help as others
in this forum. Thanks to all!!!!
 
Back
Top