| To calculate the difference between two dates, skip weekends in Visual
Basic two formulas are required, Date1 and Date2. Assign them the values of the two dates
you want to compare. For example, Date1 is Promised_ship_date, Date2 is Actual_ship_date. Date1
is the lower date. Date2 is greater. This formula does NOT account for date1 being greater
than date2.
Create another formula called DateDiff. Copy the following code into it. The single
quote will show up as remarks and not execute.
Private Sub Command1.Click()
Dim Date1 as Date
Dim Date2 as Date
Dim DateDiff as Integer
Date1="06/05/2001" ' Set to the date you want, or assign it from a data
base
Date2="06/30/2001" ' Set to the date you want
If Int((Date2 - Date1 - (6 - Datepart("w", Date1))) / 7) * 5 > 0 then
DateDiff = Int ((Date2 - Date1 - (6 - Datepart("w", Date1))) / 7) * 5
DateDiff= DateDiff + 6-DatePart ( "w",Date1) +
DatePart("w",Date2)-1
DateDiff=DateDiff - ((date2 - Date1) > 6) *2
ELSE
IF DatePart("w",Date1) > DatePart ("w", Date2) Then
DateDiff = 6 - DatePart ("w",Date1)+DatePart("w",Date2) -1
DateDiff=DateDiff - ((Date2 - Date1) > 6) *2
ELSE
DateDiff = Date2 - Date1
DateDiff = DateDiff + ((Date2 - Date1) >6) *2 ' if true, then -1, so subtract 2
Endif
Endif
Debug.Print DateDiff
End Sub
Note, See the Crystal Version of this formula for
explanations of the formula, omitted here.
Back to Mini Solve Products and more Tips
|
