|
 |
access thread: Date Difference for Workdays Only
Message #1 by pmcmillin@m... on Mon, 26 Mar 2001 16:49:47 -0600
|
|
I am needing the difference between two dates, but only counting workdays
(Monday - Friday). I found two functions on the Microsoft web site, but I
can't get either of them to work for me.
When you write a function, how exactly do you get it to a field on a form
and report? I tried to call the function and test it in the Debug window
and couldn't get it to work there either. Here are the two functions...
Public Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer
If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case WeekDay(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case WeekDay(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + WeekDay(EndDate) - WeekDay(BegDate)
End If
End Function
Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
'This function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
Pam McMillin
Systems Technician
Schlumberger - IPM Asset Management
500 W. Texas, Suite 500
P.O. Box 2726
Midland, TX 79702-2726
Office 915/571-4600
Cell 915/556-0749
Fax 915/571-4788
pmcmillin@m...
Message #2 by "John Ruff" <papparuff@c...> on Mon, 26 Mar 2001 16:28:37 -0800
|
|
Pam,
Here's code you can use. I created a form with three text boxex
(txtBeginDate, txtEndDate, txtDateDiff) and a command button (cmdRun). On
the cmdRun_Click event I entered the following code.
Private Sub cmdRun_Click()
Dim x As Integer
' I have the txtDateDiff set to 1 to
' account for the actual date in the txtBeginDate field
txtDateDiff = 1
' The function DateDiff("d",txtBeginDate, txtEndDate) determines
' how many days are between the Begin Date and the End Date
For x = 1 To DateDiff("d", txtBeginDate, txtEndDate)
' The Weekday function determines what day (1 thru 7) is being
evaluated
' The DateAdd function as 1 day to the txtBeginDate
Select Case WeekDay(DateAdd("d", x, txtBeginDate))
' if the WeedDay function is not 1 and not 7 (Saturday, Sunday)
then add
' One to the txtDateDiff field on the for
Case 2, 3, 4, 5, 6
txtDateDiff = txtDateDiff + 1
End Select
Next x
End Sub
John Ruff - The Eternal Optimist :)
-----Original Message-----
From: Pam McMillin [mailto:pmcmillin@m...]
Sent: Monday, March 26, 2001 2:50 PM
To: Access
Subject: [access] Date Difference for Workdays Only
I am needing the difference between two dates, but only counting workdays
(Monday - Friday). I found two functions on the Microsoft web site, but I
can't get either of them to work for me.
When you write a function, how exactly do you get it to a field on a form
and report? I tried to call the function and test it in the Debug window
and couldn't get it to work there either. Here are the two functions...
Public Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer
If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case WeekDay(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case WeekDay(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + WeekDay(EndDate) - WeekDay(BegDate)
End If
End Function
Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
'This function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
Pam McMillin
Systems Technician
Schlumberger - IPM Asset Management
500 W. Texas, Suite 500
P.O. Box 2726
Midland, TX 79702-2726
Office 915/571-4600
Cell 915/556-0749
Fax 915/571-4788
pmcmillin@m...
Message #3 by Bill Anton <BAnton@R...> on Tue, 27 Mar 2001 08:44:29 -0500
|
|
Pam
I found this is a book I had and it works fine for taking only workdays..
([tbl_ordership_detail]![FIRST_DATE_SHIPPED]-[tbl_ordership_detail]![ORDER_D
ATE])-(DateDiff("ww",[tbl_ordership_detail]![ORDER_DATE],[tbl_ordership_deta
il]![FIRST_DATE_SHIPPED])*2)
the datediff "ww" gives you number of weeks between dates and then * 2 for
sat and sun.
Bill
-----Original Message-----
From: pmcmillin@m...
[mailto:pmcmillin@m...]
Sent: Monday, March 26, 2001 5:50 PM
To: Access
Subject: [access] Date Difference for Workdays Only
I am needing the difference between two dates, but only counting workdays
(Monday - Friday). I found two functions on the Microsoft web site, but I
can't get either of them to work for me.
When you write a function, how exactly do you get it to a field on a form
and report? I tried to call the function and test it in the Debug window
and couldn't get it to work there either. Here are the two functions...
Public Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer
If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case WeekDay(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case WeekDay(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + WeekDay(EndDate) - WeekDay(BegDate)
End If
End Function
Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
'This function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
Pam McMillin
Systems Technician
Schlumberger - IPM Asset Management
500 W. Texas, Suite 500
P.O. Box 2726
Midland, TX 79702-2726
Office 915/571-4600
Cell 915/556-0749
Fax 915/571-4788
pmcmillin@m...
Message #4 by pmcmillin@m... on Tue, 27 Mar 2001 09:05:48 -0600
|
|
--=====================_3609089==_.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed
John,
You know I am just picking your brain on a regular basis. Hope I am not
taking up too much of your time. But I have another question. I got this
procedure to work on my form, but I am needing it to show up on
reports. Also when I move to another record the user would have to click
on the Run Command each time. Which isn't that big of a problem if I can
get it to show up on reports.
Any suggestions?
Pam
At 04:28 PM 3/26/2001 -0800, you wrote:
>Pam,
>
>Here's code you can use. I created a form with three text boxex
>(txtBeginDate, txtEndDate, txtDateDiff) and a command button (cmdRun). On
>the cmdRun_Click event I entered the following code.
>
>Private Sub cmdRun_Click()
> Dim x As Integer
>
> ' I have the txtDateDiff set to 1 to
> ' account for the actual date in the txtBeginDate field
> txtDateDiff = 1
>
> ' The function DateDiff("d",txtBeginDate, txtEndDate) determines
> ' how many days are between the Begin Date and the End Date
> For x = 1 To DateDiff("d", txtBeginDate, txtEndDate)
> ' The Weekday function determines what day (1 thru 7) is being
>evaluated
> ' The DateAdd function as 1 day to the txtBeginDate
> Select Case WeekDay(DateAdd("d", x, txtBeginDate))
> ' if the WeedDay function is not 1 and not 7 (Saturday, Sunday)
>then add
> ' One to the txtDateDiff field on the for
> Case 2, 3, 4, 5, 6
> txtDateDiff = txtDateDiff + 1
> End Select
> Next x
>
>End Sub
>
Message #5 by "John Ruff" <papparuff@c...> on Tue, 27 Mar 2001 08:37:44 -0800
|
|
Pam,
What field(s) needs to be displayed on the report?
If you don't want to have the command button, you can add the code to the
AfterUpdate property of any of the text boxes.
John Ruff - The Eternal Optimist :-)
|
|
|
View Cart
