Finding the difference between two dates 1

[ddonia]

Hello,

Maybe someone out there can help? We need a way to calculate the difference between two dates but we need to exclude any weekends and/or holiday dates. is there a way to do that in Access? In Excel they have a function called "NETWORKDAYS" but that does not appear to be an option in Access. Do we have something similiar avail to us?

Thanks!

 

[CautionMP]

If your good with code I can see if I can find a routine that I wrote about two years ago that will work for any date after 1/1/1999 and uses the standard 7 banking holidays. Reply to the thread to let me know if you are interested.

 

[ddonia]

Absolutely!! I am not an expert in VBA but can get myself through most programs.

Thanks!

 

[CautionMP]

Here it is, I believe that the logic for Thanksgiving is the flawed routine, I adjust by 21 days but if Nov 1 is a Thursday it doesn't work (it would give you the fourth Thursday). I never fixed it because if work doesn't use it, I don't get paid to fix it.
Also below the first routine is the code to run it backwards for projecting due dates.

'***Begin Code fAging***
Public Function fAging(DateOpened As Date, DateClosed As Date)
'This code developed in July 2001 for departmental use at a finacial institution
'but was never implemented due to the fear that it could not be maintained if the
'developer left. Due to this fact this function was never fully tested and if
'memory serves me correctly there was a bug in the logic for one of the holidays.
'BAE 6/24/2003

Dim dteCounter As Date
Dim intDateSpecificAdjustment As Integer, intAge As Integer

' Declare Holidays
Dim intNewYears As Date, intMLKJr As Date, intPresidents As Date, intMemorial As Date
Dim intIndependence As Date, intLaborDay As Date, intColumbus As Date, intVeterans As Date
Dim intThanksgiving As Date, intChristmas As Date

' Intialize variable
dteCounter = DateOpened

' Calculates initial completion time in actual calender days
intAge = DateDiff("d", dteCounter, DateClosed)

' This accounts for 365/366 day years when determinig the number of days to adjustment
' for holidays that fall on a particular day of the week.
' * To calculate for dates before 1/1/1999 change #.../1998# to an earlier year
intDateSpecificAdjustment = DateDiff("d", #12/31/1998#, dteCounter - DatePart("y", dteCounter))

' * If the above date is changed update every occurence of 1999 from this point on to
' * reflect one year later then the date in the previous statement
' Set value for New Years Day
intNewYears = #1/1/1999#
' Set value for Independence Day
intIndependence = #7/4/1999#
'Set value for Veterans Day
intVeterans = #11/11/1999#
' Set value for Christmas Day
intChristmas = #12/25/1999#

' Calculates Martin Luther King Jr Day for current year
intMLKJr = #1/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intMLKJr) <> 2 Then
intMLKJr = intMLKJr + 1
End If
Loop Until WeekDay(intMLKJr) = 2
intMLKJr = intMLKJr + 14

' Calculates Presidents Day for current year
intPresidents = #2/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intPresidents) <> 2 Then
intPresidents = intPresidents + 1
End If
Loop Until WeekDay(intPresidents) = 2
intPresidents = intPresidents + 14

' Calculates Memorial Day for curent year
intMemorial = #5/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intMemorial) <> 2 Then
intMemorial = intMemorial + 1
End If
Loop Until WeekDay(intMemorial) = 2
intMemorial = intMemorial + 21

' Calculates Labor Day for current year
intLaborDay = #9/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intLaborDay) <> 2 Then
intLaborDay = intLaborDay + 1
End If
Loop Until WeekDay(intLaborDay) = 2

' Calculates Columbus Day for current year
intColumbus = #10/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intColumbus) <> 2 Then
intColumbus = intColumbus + 1
End If
Loop Until WeekDay(intColumbus) = 2
intColumbus = intColumbus + 14

' Calculates Thanksgiving Day for current year
intThanksgiving = #11/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intThanksgiving) <> 5 Then
intThanksgiving = intThanksgiving + 1
End If
Loop Until WeekDay(intThanksgiving) = 5
intThanksgiving = intThanksgiving + 21

' This loop will reduce the intial intAge value to account for any
' any non-business days between DateOpen and DateClose
Do
dteCounter = dteCounter + 1

' Traps and adjusts Due Date for Sat.
If WeekDay(dteCounter) = 7 Then
intAge = intAge - 1
End If

' Traps and adjusts Due Date for Sun.
If WeekDay(dteCounter) = 1 Then
intAge = intAge - 1
End If

' Traps to remove New Years day
If dteCounter = intNewYears + intDateSpecificAdjustment Then
intAge = intAge - 1
End If

'Traps to remove Martin Luther King day
If dteCounter = intMLKJr Then
intAge = intAge - 1
End If

'Traps to remove Presidents day
If dteCounter = intPresidents Then
intAge = intAge - 1
End If

'Traps to remove Memorial day
If dteCounter = intMemorial Then
intAge = intAge - 1
End If

'Traps to remove Independence Day
If dteCounter = intIndependence + intDateSpecificAdjustment Then
intAge = intAge - 1
End If

'Traps to remove Labor Day
If dteCounter = intLaborDay Then
intAge = intAge - 1
End If

'Traps to remove Columbus Day
If dteCounter = intColumbus Then
intAge = intAge - 1
End If

' Traps to remove Veterans Day
If dteCounter = intVeterans + intDateSpecificAdjustment Then
intAge = intAge - 1
End If

' Traps to remove Thanksgiving day
If dteCounter = intThanksgiving Then
intAge = intAge - 1
End If

' Traps to remove Christmas Day
If dteCounter = intChristmas + intDateSpecificAdjustment Then
intAge = intAge - 1
End If

Loop Until DateClosed = dteCounter

' Set the function equal to the final value contained in intAge
fAging = intAge
End Function
'***End Code fAging***

'***Begin Code fBusinessDueDate***
Public Function fBusinessDueDate(SLA As Integer, DateOpened As Date)
'This code developed in July 2001 for departmental use at a finacial institution
'but was never implemented due to the fear that it could not be maintained if the
'developer left. Due to this fact this function was never fully tested and if
'memory serves me correctly there was a bug in the logic for one of the holidays.
'BAE 6/24/2003

Dim dteDue As Date
Dim intDateSpecificAdjustment As Integer

' Declare Holidays
Dim intNewYears As Date, intMLKJr As Date, intPresidents As Date, intMemorial As Date
Dim intIndependence As Date, intLaborDay As Date, intColumbus As Date
Dim intVeterans As Date, intThanksgiving As Date, intChristmas As Date

' Sets DateOpened as starting date for dteDue
dteDue = DateOpened

' This accounts for 365/366 day years when determinig the number of days to adjustment
' for holidays that fall on a particular day of the week.
' * To calculate for dates before 1/1/1999 change #.../1998# to an earlier year
intDateSpecificAdjustment = DateDiff(&quot;d&quot;, #12/31/1998#, DateOpened - DatePart(&quot;y&quot;, DateOpened))

' * If the above date is changed update every occurence of 1999 from this point on to
' * reflect one year later then the date in the previous statement
' Set value for New Years Day
intNewYears = #1/1/1999#
' Set value for Independence Day
intIndependence = #7/4/1999#
'Set value for Veterans Day
intVeterans = #11/11/1999#
' Set value for Christmas Day
intChristmas = #12/25/1999#

' Calculates Martin Luther King Jr Day for current year
intMLKJr = #1/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intMLKJr) <> 2 Then
intMLKJr = intMLKJr + 1
End If
Loop Until WeekDay(intMLKJr) = 2
intMLKJr = intMLKJr + 14

' Calculates Presidents Day for current year
intPresidents = #2/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intPresidents) <> 2 Then
intPresidents = intPresidents + 1
End If
Loop Until WeekDay(intPresidents) = 2
intPresidents = intPresidents + 14

' Calculates Memorial Day for curent year
intMemorial = #5/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intMemorial) <> 2 Then
intMemorial = intMemorial + 1
End If
Loop Until WeekDay(intMemorial) = 2
intMemorial = intMemorial + 21

' Calculates Labor Day for current year
intLaborDay = #9/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intLaborDay) <> 2 Then
intLaborDay = intLaborDay + 1
End If
Loop Until WeekDay(intLaborDay) = 2

' Calculates Columbus Day for current year
intColumbus = #10/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intColumbus) <> 2 Then
intColumbus = intColumbus + 1
End If
Loop Until WeekDay(intColumbus) = 2
intColumbus = intColumbus + 14

' Calculates Thanksgiving Day for current year
intThanksgiving = #11/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intThanksgiving) <> 5 Then
intThanksgiving = intThanksgiving + 1
End If
Loop Until WeekDay(intThanksgiving) = 5
intThanksgiving = intThanksgiving + 21

' Routine to evaluate days between Start Date and Due Date day by day
Do
dteDue = dteDue + 1
SLA = SLA - 1

' Traps and adjusts Due Date for Sat.
If WeekDay(dteDue) = 7 Then
dteDue = dteDue + 1
End If

' Traps and adjusts Due Date for Sun.
If WeekDay(dteDue) = 1 Then
dteDue = dteDue + 1
End If

' Traps to remove New Years day
If dteDue = intNewYears + intDateSpecificAdjustment Then
dteDue = dteDue + 1
End If

'Traps to remove Martin Luther King day
If dteDue = intMLKJr Then
dteDue = dteDue + 1
End If

'Traps to remove Presidents day
If dteDue = intPresidents Then
dteDue = dteDue + 1
End If

'Traps to remove Memorial day
If dteDue = intMemorial Then
dteDue = dteDue + 1
End If

'Traps to remove Independence Day
If dteDue = intIndependence + intDateSpecificAdjustment Then
dteDue = dteDue + 1
End If

'Traps to remove Labor Day
If dteDue = intLaborDay Then
dteDue = dteDue + 1
End If

'Traps to remove Columbus Day
If dteDue = intColumbus Then
dteDue = dteDue + 1
End If

' Traps to remove Veterans Day
If dteDue = intVeterans + intDateSpecificAdjustment Then
dteDue = dteDue + 1
End If

' Traps to remove Thanksgiving day
If dteDue = intThanksgiving Then
dteDue = dteDue + 1
End If

' Traps to remove Christmas Day
If dteDue = intChristmas + intDateSpecificAdjustment Then
dteDue = dteDue + 1
End If

Loop Until SLA = 0
fBusinessDueDate = dteDue
End Function

 

[CautionMP]

Here it is, I believe that the logic for Thanksgiving is the flawed routine, I adjust by 21 days but if Nov 1 is a Thursday it doesn't work (it would give you the fourth Thursday). I never fixed it because if work doesn't use it, I don't get paid to fix it.
I will submit a second thread with the function to run this backwards to project due dates.
'***Begin Code fAging***
Public Function fAging(DateOpened As Date, DateClosed As Date)
'This code developed in July 2001 for departmental use at a finacial institution
'but was never implemented due to the fear that it could not be maintained if the
'developer left. Due to this fact this function was never fully tested and if
'memory serves me correctly there was a bug in the logic for one of the holidays.
'BAE 6/24/2003

Dim dteCounter As Date
Dim intDateSpecificAdjustment As Integer, intAge As Integer

' Declare Holidays
Dim intNewYears As Date, intMLKJr As Date, intPresidents As Date, intMemorial As Date
Dim intIndependence As Date, intLaborDay As Date, intColumbus As Date, intVeterans As Date
Dim intThanksgiving As Date, intChristmas As Date

' Intialize variable
dteCounter = DateOpened

' Calculates initial completion time in actual calender days
intAge = DateDiff(&quot;d&quot;, dteCounter, DateClosed)

' This accounts for 365/366 day years when determinig the number of days to adjustment
' for holidays that fall on a particular day of the week.
' * To calculate for dates before 1/1/1999 change #.../1998# to an earlier year
intDateSpecificAdjustment = DateDiff(&quot;d&quot;, #12/31/1998#, dteCounter - DatePart(&quot;y&quot;, dteCounter))

' * If the above date is changed update every occurence of 1999 from this point on to
' * reflect one year later then the date in the previous statement
' Set value for New Years Day
intNewYears = #1/1/1999#
' Set value for Independence Day
intIndependence = #7/4/1999#
'Set value for Veterans Day
intVeterans = #11/11/1999#
' Set value for Christmas Day
intChristmas = #12/25/1999#

' Calculates Martin Luther King Jr Day for current year
intMLKJr = #1/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intMLKJr) <> 2 Then
intMLKJr = intMLKJr + 1
End If
Loop Until WeekDay(intMLKJr) = 2
intMLKJr = intMLKJr + 14

' Calculates Presidents Day for current year
intPresidents = #2/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intPresidents) <> 2 Then
intPresidents = intPresidents + 1
End If
Loop Until WeekDay(intPresidents) = 2
intPresidents = intPresidents + 14

' Calculates Memorial Day for curent year
intMemorial = #5/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intMemorial) <> 2 Then
intMemorial = intMemorial + 1
End If
Loop Until WeekDay(intMemorial) = 2
intMemorial = intMemorial + 21

' Calculates Labor Day for current year
intLaborDay = #9/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intLaborDay) <> 2 Then
intLaborDay = intLaborDay + 1
End If
Loop Until WeekDay(intLaborDay) = 2

' Calculates Columbus Day for current year
intColumbus = #10/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intColumbus) <> 2 Then
intColumbus = intColumbus + 1
End If
Loop Until WeekDay(intColumbus) = 2
intColumbus = intColumbus + 14

' Calculates Thanksgiving Day for current year
intThanksgiving = #11/1/1999# + intDateSpecificAdjustment
Do
If WeekDay(intThanksgiving) <> 5 Then
intThanksgiving = intThanksgiving + 1
End If
Loop Until WeekDay(intThanksgiving) = 5
intThanksgiving = intThanksgiving + 21

' This loop will reduce the intial intAge value to account for any
' any non-business days between DateOpen and DateClose
Do
dteCounter = dteCounter + 1

' Traps and adjusts Due Date for Sat.
If WeekDay(dteCounter) = 7 Then
intAge = intAge - 1
End If

' Traps and adjusts Due Date for Sun.
If WeekDay(dteCounter) = 1 Then
intAge = intAge - 1
End If

' Traps to remove New Years day
If dteCounter = intNewYears + intDateSpecificAdjustment Then
intAge = intAge - 1
End If

'Traps to remove Martin Luther King day
If dteCounter = intMLKJr Then
intAge = intAge - 1
End If

'Traps to remove Presidents day
If dteCounter = intPresidents Then
intAge = intAge - 1
End If

'Traps to remove Memorial day
If dteCounter = intMemorial Then
intAge = intAge - 1
End If

'Traps to remove Independence Day
If dteCounter = intIndependence + intDateSpecificAdjustment Then
intAge = intAge - 1
End If

'Traps to remove Labor Day
If dteCounter = intLaborDay Then
intAge = intAge - 1
End If

'Traps to remove Columbus Day
If dteCounter = intColumbus Then
intAge = intAge - 1
End If

' Traps to remove Veterans Day
If dteCounter = intVeterans + intDateSpecificAdjustment Then
intAge = intAge - 1
End If

' Traps to remove Thanksgiving day
If dteCounter = intThanksgiving Then
intAge = intAge - 1
End If

' Traps to remove Christmas Day
If dteCounter = intChristmas + intDateSpecificAdjustment Then
intAge = intAge - 1
End If

Loop Until DateClosed = dteCounter

' Set the function equal to the final value contained in intAge
fAging = intAge
End Function
'***End Code fAging***

 

[CautionMP]

The first post timed out on my side but made it to the thread, that is why the first routine is duplicated!?!

 

[Poteen]

I believe there's a much easier way:
You CAN use the excel commands directly if you set the references to the excel object library and use the Application method!!
John

 

[MichaelRed]

see faq181-261




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CautionMP]

Using a table as in FAQ181-261 is an easy way to adjust for holidays but it requires that the holiday be manually populated, the beauty of the above routines is that they will automatically adjust for for all holidays in any year (after 1999) without using any database resources (record space)or DBA time (to update the table) and helps to eliminate data entry errors on dates (for you outlook users remember when Bill Gates moved Thanksgiving in 2001?).

 

[MichaelRed]

On hte other hand, in most 'real world' environments (at least where I have worked) the 'observation date' of many Holidays wasn't the 'official date' for many of the Holidays, so the &quot;Auto Calculation&quot; was mostly a waste.

Further complicating 'my life' in holiday land was the simple 'determination' of WHAT holidays the particular 'workplace' would observe.

For the &quot;official&quot; holiday dates, you can get a list from Win itself without any calculation, although for any 'real world' business you would need to edit the list (extensively) as the Win list is ~~ 10 to 20 * what any business observes.

With the vagaries of 'observation' dates and variation of the number (and even the &quot;Which ones&quot, I find it quite a bit easier to simply aupply the function along with a small form (for dept / group / company admins) to maintain the listing. When ever the holiday list for a period is published, they have the responsability of updating the table via provided form.

Ynote that I need to maintain table - but even a cursory glance at the previously posted routine notes that at least one of the date calcs is (appears to be?) flawed and that the routien was never (commercially) implemented, and requires modificattions to account for the use in years prior to 1999. While the latter becomes increasing irrelevant, the concept of needing programming attention to a simple function on a regular basis causes some degree of caution on my part.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places