I am working in vb2005. how can I calculate business days (not including
holidays and weekends) between 2 dates? thanks
Al
holidays and weekends) between 2 dates? thanks
Al
-
-
Re: I need help with calculating business days
On Apr 15, 10:28 am, Al <A...@discussio ns.microsoft.co mwrote:It's pretty easy to determine weekends, but how are you going toI am working in vb2005. how can I calculate business days (not including
holidays and weekends) between 2 dates? thanks
Al
determine what a holiday is? Are you going off your company's holiday
schedule?
Thanks,
Seth Rowe [MVP]
-
Re: I need help with calculating business days
Hello Al,
You can get the number of days between two dates by using the DateDiff
function. To calculate the number of business days, you can use this code:
Private Sub Test()
Dim Date1 As New Date(2008, 4, 15)
Dim Date2 As New Date(2008, 5, 31)
MsgBox("There are " & CStr(CalculateB usinessDays(Dat e1, Date2, 6) & _
" business days" & vbCrLf & "for the time frame of " & _
Date1.ToShortDa teString & " - " & Date2.ToShortDa teString))
End Sub
Private Function CalculateBusine ssDays(ByVal Date1 As Date, ByVal _
Date2 As Date, ByVal BusinessDaysPer Week As Long) As Long
Dim Weeks As Long, BusinessDays As Long
Dim tDate As Date = Date1
If Not Weekday(tDate) = 1 Then
'Week starts with Sunday. If the date is not a sunday, add the
'required days.
tDate = DateAdd(DateInt erval.Day, 8 - Weekday(tDate), tDate)
End If
'Calculate whole weeks.
Weeks = Fix(CDec(DateDi ff(DateInterval .Day, tDate, Date2)) / CDec(7))
'Calculate business days for the previously calculated weeks.
BusinessDays = Weeks * BusinessDaysPer Week
If Not (Date1.Equals(t Date)) Then
'If we had to add days before, reverse this now by adding the
'required days to BusinessDays.
BusinessDays += DateDiff(DateIn terval.Day, Date1, tDate)
End If
'If the second date is not a sunday, add the required days.
BusinessDays += Weekday(Date2) - 1
'Calculation complete.
Return BusinessDays
End Function
Best regards,
Martin
Am 15.04.2008 22:28, schrieb Al:I am working in vb2005. how can I calculate business days (not including
holidays and weekends) between 2 dates? thanks
Al
>Comment
-
Re: I need help with calculating business days
not sure but this Weeks = Fix(CDec(DateDi ff(DateInterval .Day, tDate,
Date2)) / CDec(7))
complains about a decimical to long conversion and when I do Weeks =
cint(Fix(CDec(D ateDiff(DateInt erval.Day, tDate, Date2)) / CDec(7))) the
calcuation is not correct?
"Martin H." <hkshk@gmx.netw rote in message news:4804ce44$1 @127.0.0.1...Hello Al,
>
You can get the number of days between two dates by using the DateDiff
function. To calculate the number of business days, you can use this code:
>
Private Sub Test()
Dim Date1 As New Date(2008, 4, 15)
Dim Date2 As New Date(2008, 5, 31)
>
MsgBox("There are " & CStr(CalculateB usinessDays(Dat e1, Date2, 6) & _
" business days" & vbCrLf & "for the time frame of " & _
Date1.ToShortDa teString & " - " & Date2.ToShortDa teString))
End Sub
>
Private Function CalculateBusine ssDays(ByVal Date1 As Date, ByVal _
Date2 As Date, ByVal BusinessDaysPer Week As Long) As Long
Dim Weeks As Long, BusinessDays As Long
Dim tDate As Date = Date1
>
If Not Weekday(tDate) = 1 Then
'Week starts with Sunday. If the date is not a sunday, add the
'required days.
tDate = DateAdd(DateInt erval.Day, 8 - Weekday(tDate), tDate)
End If
>
'Calculate whole weeks.
Weeks = Fix(CDec(DateDi ff(DateInterval .Day, tDate, Date2)) / CDec(7))
>
'Calculate business days for the previously calculated weeks.
BusinessDays = Weeks * BusinessDaysPer Week
>
If Not (Date1.Equals(t Date)) Then
'If we had to add days before, reverse this now by adding the
'required days to BusinessDays.
BusinessDays += DateDiff(DateIn terval.Day, Date1, tDate)
End If
>
'If the second date is not a sunday, add the required days.
BusinessDays += Weekday(Date2) - 1
>
'Calculation complete.
Return BusinessDays
End Function
>
Best regards,
>
Martin
>
Am 15.04.2008 22:28, schrieb Al:>I am working in vb2005. how can I calculate business days (not including
>holidays and weekends) between 2 dates? thanks
>Al
>>
Comment
-
Re: I need help with calculating business days
Hello Brian,
I usually have the option "Strict" set to "Off" while your's is set to
"On"...
Weeks = CLng(Fix(CDec(D ateDiff(DateInt erval.Day, tDate, Date2)) /
CDec(7)))
Should do the trick (since "Weeks" is a Long variable, you need to use
CLng).
Best regards,
Martin
On Apr 16, 6:48 am, "Brian S." <bsgalla...@com munity.nospamwr ote:not sure but this Weeks = Fix(CDec(DateDi ff(DateInterval .Day, tDate,
Date2)) / CDec(7))
complains about a decimical to long conversion and when I do Weeks =
cint(Fix(CDec(D ateDiff(DateInt erval.Day, tDate, Date2)) / CDec(7))) the
calcuation is not correct?
>
"Martin H." <hk...@gmx.netw rote in messagenews:480 4ce44$1@127.0.0 .1...>Hello Al,>You can get the number of days between two dates by using the DateDiff
function. To calculate the number of business days, you can use this code:>Private Sub Test()
Dim Date1 As New Date(2008, 4, 15)
Dim Date2 As New Date(2008, 5, 31)>MsgBox("There are " & CStr(CalculateB usinessDays(Dat e1, Date2, 6) & _
" business days" & vbCrLf & "for the time frame of " & _
Date1.ToShortDa teString & " - " & Date2.ToShortDa teString))
End Sub>Private Function CalculateBusine ssDays(ByVal Date1 As Date, ByVal _
Date2 As Date, ByVal BusinessDaysPer Week As Long) As Long
Dim Weeks As Long, BusinessDays As Long
Dim tDate As Date = Date1>If Not Weekday(tDate) = 1 Then
'Week starts with Sunday. If the date is not a sunday, add the
'required days.
tDate = DateAdd(DateInt erval.Day, 8 - Weekday(tDate), tDate)
End If>'Calculate whole weeks.
Weeks = Fix(CDec(DateDi ff(DateInterval .Day, tDate, Date2)) / CDec(7))>'Calculate business days for the previously calculated weeks.
BusinessDays = Weeks * BusinessDaysPer Week>If Not (Date1.Equals(t Date)) Then
'If we had to add days before, reverse this now by adding the
'required days to BusinessDays.
BusinessDays += DateDiff(DateIn terval.Day, Date1, tDate)
End If>'If the second date is not a sunday, add the required days.
BusinessDays += Weekday(Date2) - 1>'Calculation complete.
Return BusinessDays
End Function>Best regards,>MartinAm 15.04.2008 22:28, schrieb Al:I am working in vb2005. how can I calculate business days (not including
holidays and weekends) between 2 dates? thanks
AlComment
-
RE: I need help with calculating business days
Thnks to every one. I will try this and get back
"Al" wrote:
I am working in vb2005. how can I calculate business days (not including
holidays and weekends) between 2 dates? thanks
Al
>Comment
-
Re: I need help with calculating business days
On Apr 16, 9:40 am, Al <A...@discussio ns.microsoft.co mwrote:I am not sure if this will help you but, I created this program inThnks to every one. I will try this and get back
>
>
>
"Al" wrote:>I am working in vb2005. how can I calculate business days (not including
holidays and weekends) between 2 dates? thanks
Al- Hide quoted text -
- Show quoted text -
1991 using the "Natral Adabas language" on a mainframe computer. This
was written as a callable subroutine, I think even if you do not know
the NATURAL language you can figure out what this program does. The
holdiays are the State of Washington holdiays
0020 ** PROGRAM : NXTWKDAY COMPUTES NEXT WORKING DAY.
0030 ** WRITTEN BY: R NESBITT
0040 ** CREATED : MAY OF 1991
0050 **
0060 ** COMPUTES THE FOLLOWING HOLIDAYS . . .
0070 ** NEW YEARS DAY = JAN 1
0080 ** MARTIN LUTHER KINGS B-DAY = 3RD MONDAY IN JAN
0090 ** PRESIDENTS DAY = 3RD MONDAY IN FEB
0100 ** MEMORIAL DAY = LAST MONDY IN MAY
0110 ** INDEPENDENCE DAY = 4TH OF JULY
0120 ** LABOR DAY = 1ST MONDAY IN SEPTEMBER
0130 ** VETERANS DAY = 11TH OF NOV
0140 ** THANKSGIVING = 4TH THURSDAY AND FRIDAY IN NOV
0150 ** CHRISTMAS = 25TH OF DEC
0160 **
0170 ** IF A HOLIDAY FALLS ON A SATURDAY THEN THE HOLIDAY IS FRIDAY.
0180 ** IF A HOLIDAY FALLS ON A SUNDAY THEN THE HOLIDAY IS MONDAY.
0190 **
0200 **
0620 **
0630 DEFINE DATA PARAMETER
0640 1 #NEXT-WRK-DAY (D)
0650 LOCAL
0660 1 #INPUT-DATE (D)
0670 1 #NEXT-DAY (D)
0680 1 #DAYOWK (A2)
0690 1 #WRK-MONTH (A2)
0700 1 #WRK-DAY (A2)
0710 1 #THURSDAY (A2)
0720 1 #FRIDAY (A2)
0730 1 #SATURDAY (A2)
0740 1 #SUNDAY (A2)
0750 1 #MONDAY (A2)
0760 END-DEFINE
0770 *
0780 * THIS MOVES THE FIRST TWO CHARACTERS TO THE DAY IN UPPER LOWER
CASE
0790 *
0800 MOVE H'E388' TO #THURSDAY /* Th
0810 MOVE H'C699' TO #FRIDAY /* Fr
0820 MOVE H'E281' TO #SATURDAY /* Sa
0830 MOVE H'E2A4' TO #SUNDAY /* Su
0840 MOVE H'D496' TO #MONDAY /* Mo
0850 *
0860 IF #NEXT-WRK-DAY GT 0
0870 DO
0880 MOVE #NEXT-WRK-DAY TO #INPUT-DATE
0990 DOEND /* (0870)
1000 ELSE
1010 MOVE *DATX TO #INPUT-DATE
1020 *
1030 COMPUTE #NEXT-DAY = #INPUT-DATE + 1
1040 *
1050 MOVE EDITED #NEXT-DAY (EM=NN) TO #DAYOWK
1060 MOVE EDITED #NEXT-DAY (EM=MM) TO #WRK-MONTH
1070 *
1080 DECIDE ON FIRST VALUE OF #DAYOWK
1090 VALUE #SATURDAY
1100 COMPUTE #NEXT-WRK-DAY = #NEXT-DAY + 2
1110 VALUE #SUNDAY
1120 COMPUTE #NEXT-WRK-DAY = #NEXT-DAY + 1
1130 NONE
1140 MOVE #NEXT-DAY TO #NEXT-WRK-DAY
1150 END-DECIDE /* (1080)
1160 *
1170 MOVE EDITED #NEXT-WRK-DAY (EM=DD) TO #WRK-DAY
1180 MOVE EDITED #NEXT-WRK-DAY (EM=NN) TO #DAYOWK
1190 MOVE EDITED #NEXT-WRK-DAY (EM=MM) TO #WRK-MONTH
1200 DECIDE ON FIRST VALUE OF #WRK-MONTH
1210 VALUE '01'
1220 PERFORM COMPUTE-JAN
1230 VALUE '02'
1240 IF #WRK-DAY = MASK(15-21) AND #DAYOWK = #MONDAY /*PRES DAY
*/
1250 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
1260 VALUE '05'
1270 IF #WRK-DAY = MASK(25-31) AND #DAYOWK = #MONDAY /*MEMORIAL
DAY*/
1280 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
1290 VALUE '07'
1300 PERFORM COMPUTE-JUL
1310 VALUE '09'
1320 IF #WRK-DAY = MASK(01-07) AND #DAYOWK = #MONDAY /*LABOR
DAY*/
1330 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
1340 VALUE '11'
1350 PERFORM COMPUTE-NOV
1360 VALUE '12'
1370 PERFORM COMPUTE-DEC
1380 NONE
1390 IGNORE
1400 END-DECIDE /* (1200)
1410 *
1420 *************** **************
1430 DEFINE SUBROUTINE COMPUTE-JAN
1440 *************** **************
1450 *
1460 IF #WRK-DAY = MASK(01-02) /**** NEW YEARS DAY ****/
1470 DO
1480 DECIDE ON FIRST VALUE #DAYOWK
1490 VALUE #FRIDAY
1500 IF #WRK-DAY = MASK(01-01)
1510 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 3
1520 VALUE #MONDAY
1530 IF #WRK-DAY = MASK(01-02)
1540 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
1550 NONE
1560 IF #WRK-DAY = '01'
1570 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
1580 END-DECIDE /* (1480)
1590 DOEND /* (1470)
1600 *
1610 ELSE /**** MLK JR BIRTHDAY ****/
1620 IF #WRK-DAY = MASK(15-21) AND #DAYOWK = #MONDAY
1630 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
1640 RETURN /* (1430)
1650 *************** **************
1660 DEFINE SUBROUTINE COMPUTE-JUL
1670 *************** **************
1680 *
1690 IF #WRK-DAY = MASK(03-06) /**** INDEPENDENCE DAY ****/
1700 DO
1710 DECIDE ON FIRST VALUE #DAYOWK
1720 VALUE #FRIDAY
1730 IF #WRK-DAY = MASK(03-04)
1740 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 3
1750 VALUE #MONDAY
1760 IF #WRK-DAY = MASK(04-05)
1770 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
1780 NONE
1790 IF #WRK-DAY = '04'
1800 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
1810 END-DECIDE /* (1710)
1820 DOEND /* (1700)
1830 *
1840 RETURN /* (1660)
1850 *************** **************
1860 DEFINE SUBROUTINE COMPUTE-NOV
1870 *************** **************
1880 *
1890 IF #WRK-DAY = MASK(10-13) /**** VETERANS DAY ****/
1900 DO
1910 DECIDE ON FIRST VALUE #DAYOWK
1920 VALUE #FRIDAY
1930 IF #WRK-DAY = MASK(10-11)
1940 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 3
1950 VALUE #MONDAY
1960 IF #WRK-DAY = MASK(11-12)
1970 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
1980 NONE
1990 IF #WRK-DAY = '11'
2000 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
2010 END-DECIDE /* (1910)
2020 DOEND /* (1900)
2030 ELSE /* (1890)
2040 IF #WRK-DAY = MASK(22-28) /*** THANKSGIVING HOLIDAYS ***/
2050 DO
2060 DECIDE ON FIRST VALUE #DAYOWK
2070 VALUE #THURSDAY
2080 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 4
2090 VALUE #FRIDAY
2100 IF #WRK-DAY NE '22'
2110 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 3
2120 NONE
2130 IGNORE
2140 END-DECIDE /* (2060)
2150 DOEND /* (2050)
2160 ELSE
2170 IF #WRK-DAY = '29' AND #DAYOWK = #FRIDAY
2180 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 3
2190 *
2200 RETURN /* (1860)
2210 *************** **************
2220 DEFINE SUBROUTINE COMPUTE-DEC
2230 *************** **************
2240 *
2250 IF #WRK-DAY = MASK(24-27) /**** CHRISTMAS DAY ****/
2260 DO
2270 DECIDE ON FIRST VALUE #DAYOWK
2280 VALUE #FRIDAY
2290 IF #WRK-DAY = MASK(24-25)
2300 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 3
2310 VALUE #MONDAY
2320 IF #WRK-DAY = MASK(25-26)
2330 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
2340 NONE
2350 IF #WRK-DAY = '25'
2360 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 1
2370 END-DECIDE /* (2270)
2380 DOEND /* (2260)
2390 ELSE /* (2250)
2400 IF #WRK-DAY = '31' AND #DAYOWK = #FRIDAY /* NEW YEARS HOLIDAY
****/
2410 COMPUTE #NEXT-WRK-DAY = #NEXT-WRK-DAY + 3
2420 *
2430 RETURN /* (2220)
2440 END
Comment
-
Re: I need help with calculating business days
What advantage do you have over having this option set to "Off". just
curious.. i'll look it up later though.. just wanted your opinion.
"HKSHK" <hkshk@gmx.netw rote in message
news:11ca2913-c4c8-46d9-a872-9594d5e4bab9@k1 g2000prb.google groups.com...Hello Brian,
>
I usually have the option "Strict" set to "Off" while your's is set to
"On"...
>
Weeks = CLng(Fix(CDec(D ateDiff(DateInt erval.Day, tDate, Date2)) /
CDec(7)))
>
Should do the trick (since "Weeks" is a Long variable, you need to use
CLng).
>
Best regards,
>
Martin
>
On Apr 16, 6:48 am, "Brian S." <bsgalla...@com munity.nospamwr ote:>>not sure but this Weeks = Fix(CDec(DateDi ff(DateInterval .Day, tDate,
>Date2)) / CDec(7))
> complains about a decimical to long conversion and when I do Weeks =
>cint(Fix(CDec( DateDiff(DateIn terval.Day, tDate, Date2)) / CDec(7))) the
>calcuation is not correct?
>>
>"Martin H." <hk...@gmx.netw rote in messagenews:480 4ce44$1@127.0.0 .1...>>Hello Al,>>You can get the number of days between two dates by using the DateDiff
function. To calculate the number of business days, you can use this
code:>>Private Sub Test()
Dim Date1 As New Date(2008, 4, 15)
Dim Date2 As New Date(2008, 5, 31)>>MsgBox("There are " & CStr(CalculateB usinessDays(Dat e1, Date2, 6) & _
" business days" & vbCrLf & "for the time frame of " & _
Date1.ToShortDa teString & " - " & Date2.ToShortDa teString))
End Sub>>Private Function CalculateBusine ssDays(ByVal Date1 As Date, ByVal _
Date2 As Date, ByVal BusinessDaysPer Week As Long) As Long
Dim Weeks As Long, BusinessDays As Long
Dim tDate As Date = Date1>>If Not Weekday(tDate) = 1 Then
'Week starts with Sunday. If the date is not a sunday, add the
'required days.
tDate = DateAdd(DateInt erval.Day, 8 - Weekday(tDate), tDate)
End If>>'Calculate whole weeks.
Weeks = Fix(CDec(DateDi ff(DateInterval .Day, tDate, Date2)) / CDec(7))>>'Calculate business days for the previously calculated weeks.
BusinessDays = Weeks * BusinessDaysPer Week>>If Not (Date1.Equals(t Date)) Then
'If we had to add days before, reverse this now by adding the
'required days to BusinessDays.
BusinessDays += DateDiff(DateIn terval.Day, Date1, tDate)
End If>>'If the second date is not a sunday, add the required days.
BusinessDays += Weekday(Date2) - 1>>'Calculation complete.
Return BusinessDays
End Function>>Best regards,>>MartinAm 15.04.2008 22:28, schrieb Al:
>I am working in vb2005. how can I calculate business days (not
>including
>holidays and weekends) between 2 dates? thanks
>Al
Comment
-
Re: I need help with calculating business days
Hello Brian,
One advantage is that you don't have to convert data types into each
other (it's like in the old VB6 days) as VB will do that for you.
Best regards,
Martin
Am 17.04.2008 05:23, schrieb Brian S.:What advantage do you have over having this option set to "Off". just
curious.. i'll look it up later though.. just wanted your opinion.
>
"HKSHK" <hkshk@gmx.netw rote in message
news:11ca2913-c4c8-46d9-a872-9594d5e4bab9@k1 g2000prb.google groups.com...>>Hello Brian,
>>
>I usually have the option "Strict" set to "Off" while your's is set to
>"On"...
>>
>Weeks = CLng(Fix(CDec(D ateDiff(DateInt erval.Day, tDate, Date2)) /
>CDec(7)))
>>
>Should do the trick (since "Weeks" is a Long variable, you need to use
>CLng).
>>
>Best regards,
>>
>Martin
>>
>On Apr 16, 6:48 am, "Brian S." <bsgalla...@com munity.nospamwr ote:>>not sure but this Weeks = Fix(CDec(DateDi ff(DateInterval .Day, tDate,
>>Date2)) / CDec(7))
>> complains about a decimical to long conversion and when I do Weeks =
>>cint(Fix(CDec (DateDiff(DateI nterval.Day, tDate, Date2)) / CDec(7))) the
>>calcuation is not correct?
>>>
>>"Martin H." <hk...@gmx.netw rote in messagenews:480 4ce44$1@127.0.0 .1...
>>>Hello Al,
>>>You can get the number of days between two dates by using the DateDiff
>>>function. To calculate the number of business days, you can use this
>>>code:
>>>Private Sub Test()
>>>Dim Date1 As New Date(2008, 4, 15)
>>>Dim Date2 As New Date(2008, 5, 31)
>>>MsgBox("Ther e are " & CStr(CalculateB usinessDays(Dat e1, Date2, 6) & _
>>>" business days" & vbCrLf & "for the time frame of " & _
>>>Date1.ToShor tDateString & " - " & Date2.ToShortDa teString))
>>>End Sub
>>>Private Function CalculateBusine ssDays(ByVal Date1 As Date, ByVal _
>>>Date2 As Date, ByVal BusinessDaysPer Week As Long) As Long
>>>Dim Weeks As Long, BusinessDays As Long
>>>Dim tDate As Date = Date1
>>>If Not Weekday(tDate) = 1 Then
>>>'Week starts with Sunday. If the date is not a sunday, add the
>>> 'required days.
>>>tDate = DateAdd(DateInt erval.Day, 8 - Weekday(tDate), tDate)
>>>End If
>>>'Calculate whole weeks.
>>>Weeks = Fix(CDec(DateDi ff(DateInterval .Day, tDate, Date2)) / CDec(7))
>>>'Calculate business days for the previously calculated weeks.
>>>BusinessDa ys = Weeks * BusinessDaysPer Week
>>>If Not (Date1.Equals(t Date)) Then
>>>'If we had to add days before, reverse this now by adding the
>>> 'required days to BusinessDays.
>>>BusinessDa ys += DateDiff(DateIn terval.Day, Date1, tDate)
>>>End If
>>>'If the second date is not a sunday, add the required days.
>>>BusinessDa ys += Weekday(Date2) - 1
>>>'Calculati on complete.
>>>Return BusinessDays
>>>End Function
>>>Best regards,
>>>Martin
>>>Am 15.04.2008 22:28, schrieb Al:
>>>>I am working in vb2005. how can I calculate business days (not
>>>>including
>>>>holidays and weekends) between 2 dates? thanks
>>>>Al
>Comment
Comment