Calculating Days between 2 dates (excluding weekends and a twist) 2

[iuianj07]

Hello guys,

I need help in calculating number of working days between 2 dates with a little twist...

I have a Job Tracking database wherein a record (a loan/work we do) in the database goes through a different phases/status to track status of the record (Loan received, In Process, Ready for Review, Sent to Client etc)


For a quick background here is my table structure (that relates to my question):

tblJobTracking:

SitusID -- PK
WeekNumber
DealName
AssetType
PropertyCount
Etc (other fields)

tblDealStatus

DealStatusID -- PK
SitusID -- FK to tblJobTracking
Analyst -- user who is assigned for the record/deal
StatusChangeID -- FK to tblStatusChange
StatusDate -- Date
StatusHours -- how many hours did the user worked on that specific status

tblStatusChange

StatusChangeID -- PK
Status -- different status/phase (Unassigned, Received, In Process etc)

tblStatusChange:

StatusChangeID Status
1 Unassigned (also when we received the deal)
2 In Process
3 Ready for Initial Review
4 Initial Review
5 Sent Back for Corrections
6 Ready for Final Review
7 Final Review
8 Sent back to Client
9 On Hold
10 Dropped

We are now trying to create a query that will calculate how many days did a deal/record take before it was sent back to the client

What I have so far is I created these queries:

qryDateReceived

SELECT tblDealStatus.SitusID, tblDealStatus.StatusChangeID, tblDealStatus.StatusDate
FROM tblDealStatus
WHERE (((tblDealStatus.StatusChangeID)=1))
ORDER BY tblDealStatus.SitusID;

qryDateSent

SELECT tblDealStatus.SitusID, tblDealStatus.StatusChangeID, tblDealStatus.StatusDate
FROM tblDealStatus
WHERE (((tblDealStatus.StatusChangeID)=8))
ORDER BY tblDealStatus.SitusID;

then I researched for a module in the internet to calculate how many days excluding weekends...

Option Compare Database
Option Explicit
Function DateDiffExclude2(pstartdte As Date, _
                         penddte As Date, _
                         pexclude As String) As Integer
'*********************************************
'Purpose:   Return number of days between two
'           dates, excluding weekdays(Sun = 1
'           thru Sat = 7) specified by user
'Coded by:  raskew
'Inputs:    from debug (immediate) window:
'           -excludes Saturday (7) and Sunday (1) from count
'           ? DateDiffExclude2(#2/10/06#, #7/13/06#, "17")
'Output:    110
'*********************************************

Dim WeekHold  As String
Dim WeekKeep  As String
Dim FullWeek  As Integer
Dim OddDays   As Integer
Dim n         As Integer

    WeekHold = "1234567123456"
    'get # of full weeks (7 days) & convert to # of days
    FullWeek = Int((penddte - pstartdte + 1) / 7) * (7 - Len(pexclude))
    'get # of days remaining after FullWeek is determined
    OddDays = (penddte - pstartdte + 1) Mod 7
    'string representation of the weekdays contained in OddDays
    WeekKeep = Mid(WeekHold, Weekday(pstartdte), OddDays)
    'use boolean statement to reduce OddDays by 1 for each
    'pexclude weekday found in WeekKeep
    For n = 1 To Len(pexclude)
      OddDays = OddDays + (InStr(WeekKeep, Mid(pexclude, n, 1)) > 0)
    Next n
    
    DateDiffExclude2 = FullWeek + OddDays

End Function

then I created another query:

qryDifference

SELECT qryDateReceived.SitusID, qryDateReceived.StatusDate, qryDateSent.StatusDate, qryDateDiffExclude2([qryDateReceived].StatusDate,[qryDateSent].StatusDate,"17") AS WkDays
FROM qryDateReceived INNER JOIN qryDateSent ON qryDateReceived.SitusID = qryDateSent.SitusID;

It's a little weird that there are right and wrong results from qryDifference.

sample output:

SitusID DateReceived.StatusDate DateSent.StatusDate WkDays
1 9/10/2010 9/21/2010 8
2 9/10/2010 9/16/2010 5
9 9/10/2010 9/16/2010 0
27 9/14/2010 9/15/2010 2
33 9/15/2010 9/15/2010 1



based on the sample output above, SitusID 2 and 9 have the same Received and Sent date, it however returns a different WkDay number?


Also, with SitusID 33, it returns WkDays = 1, but is there a way that if we received and sent back a deal within the same day, it'll return WkDays = Same Day, and with SitusID WkDays = 1?

I could pretty much change the column expression to:

WkDays: DateDiffExclude2([DateReceived].[StatusDate],[DateSent].[StatusDate],"17")-1


But I am still having problems with SitusID 9 giving WkDays = 0 and if a deal has been received and sent back at the same day, WkDays = Same Day.


Please... any help is greatly appreciated.


Thank you...

 

[MichaelRed]

To old (or lazy) to go through the code ... there are s few date diff routines in the tek-tips fora (particularly MS Access) which do an EXCELLENT job of returning the (consistiently correct) results for this.

Some (at least ONE) adds a 'feature' which permits the exclusion of holidays (seems to me and several others to always arise when calculating the "Work Days", my overall recommendation would be to find / apply one of these soloutions.



MichaelRed

 

[MichaelRed]

faq181-261

MichaelRed

 

[smedvid]

In a past life, I actually created a table which contained all 365 days with various flags to account for different situations. In my case, I need to account for 1/2 work days which were close to holidays, as well as other days that were Official Holidays but we needed to treat them as a normal workday. Just my (2) cents... I have also used the same example that MichaelRed listed with great success... I hope this was helpful... I also work with loan data... it is a pain at times. Good luck.



Steve Medvid
IT Consultant & Web Master

 

[ZiggyS1]

I copied your module and replicated the table and my results work?

DateRec DateSent Expr1
10/09/2010 21/09/2010 8
10/09/2010 16/09/2010 5
10/09/2010 16/09/2010 5
14/09/2010 15/09/2010 2
15/09/2010 15/09/2010 1

maybe just make sure your Access is up to date as a precaution?

 

[iuianj07]

Thanks MichaelRed!

Thanks for the link and I was now able to count how many days between two dates... I am still having some issues though of some records calculating the correct cound of days, but there are a few records too that is not calculating correctly? here is a sample output:

SitusID DateReceived DateSent WkDays
1 9/10/2010 9/21/2010 7
2 9/10/2010 9/22/2010 8
5 9/10/2010 9/16/2010 4
6 9/10/2010 9/15/2010 3
8 9/10/2010 9/15/2010 2
112 9/27/2010 9/27/2010 Same Day
113 9/27/2010 9/28/2010 1



So based on the sample output above, SitusID 8 returns WkDays = 2 where it should be 3 like SitusID 6. Don't know why it's giving a different WkDays value since it has the same date?

Below is the module from the link you provided me:

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

    'Get the number of workdays between the given dates

    Dim dbs As Database
    Dim rstHolidays As Recordset

    Dim Idx As Long
    Dim MyDate As Date
    Dim NumDays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1

    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    NumSgn = Chr(35)

    MyDate = Format(StartDate, "Short Date")

    For Idx = CLng(StartDate) To CLng(EndDate)
        Select Case (Weekday(MyDate))
            Case Is = 1     'Sunday
                'Do Nothing, it is NOT a Workday

            Case Is = 7     'Saturday
                'Do Nothing, it is NOT a Workday

            Case Else       'Normal Workday
                strCriteria = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & NumSgn 'Thanks to "RoyVidar" 2/18/04
               rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    NumDays = NumDays + 1
                 Else
                    'Do Nothing, it is NOT a Workday
                End If

        End Select

        MyDate = DateAdd("d", 1, MyDate)

    Next Idx

    DeltaDays = NumDays

End Function

then on my query:

SELECT
 qryDateReceived.SitusID,
 qryDateReceived.StatusDate,
 qryDateSent.StatusDate,
 IIf((DeltaDays([qryDateReceived].StatusDate,[qryDateSent].StatusDate)-1)=0,"Same Day",DeltaDays([qryDateReceived].StatusDate,[qryDateSent].StatusDate)-1) AS WkDays,
 qryDateReceived.PropertyCount
FROM
 qryDateReceived 
INNER JOIN
 qryDateSent 
ON
 qryDateReceived.SitusID = qryDateSent.SitusID;

Any help is greatly appreciated...

 

[iuianj07]

hey Ziggy,

Thanks for trying it out? What do you mean by make sure my Access is up to date? I am using Access 2007, and hoping it is updated and should be running fine... unless you're talking about another way to update?

 

[iuianj07]

Okay, not sure if this causes the query not calculating some of the days correctly... but on my tblDealStatus and StatusDate field, I have it formatted: m/d/yy h:nn AM/PM;@

then on qryDateReceived and qrtDateSent, I've set StatusDate's property to ShortDate, because for this particular query, we shouldn't care about the time.

reason being is that, they also want to track the exact time whenever we receive a new deal...

Let me know if this helps...

Thanks

 

[ZiggyS1]

I just meant latest updates in case it was a bug

 

[iuianj07]

Well I've been re-opening the database multiple time, re-creating the queries again but it still gives the same results as I've posted earlier, so I as much as I hope it's just a weird bug that'll fix itself, it is not...

thanks

 

[dhookom]

I find a couple issues with the code. Since your date/time fields might contain a time portion, you should not use clng() since this will round to the nearest date value. For instance as I reply to this:
[tt][blue]
CLng(Now()) = 40599
CLng(Date()) = 40598
[/blue][/tt]
I would replace CLng() with Int() since this will return the date you expect.

The other issue is you have a calculation that might return either an number or the string "Same Day". IMO this is wrong. I would return the expected 0 rather than a string value.


Duane
Hook'D on Access
MS Access MVP

 

[ZiggyS1]

also add this to the module

penddte = DateValue(penddte)
pstartdte = DateValue(pstartdte)

 

[iuianj07]

hello dhookom,

Thank you, once again you have helped me alot!

At first glance esp. with SitusID 8, it now returns WkDays = 3 like SitusID 6...

I understand your point about my calculation returning either a number or string... but this is how the Project Manager wants the query to look like... Is there any workaround on this to calculate it the correct way?

Thank you very much Duane...

 

[dhookom]

Are the results as you expect now or do you still need assistance?

Typically I don't allow a user to see a query. All results should be displayed in forms where you can use the Format property to display a 0 as "Same Day". The value is still 0 but your Project Manager sees "Same Day".

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

With your expertise Duane, the results are as I expect now..

Thank you very much...

In terms of the query, unfortunately that is something very hard to implement in our office, Project Managers, Project Coordinators, Directors, VPs and almost all other users of this office are aware that they can view queries on the left pane of the database instead of creating forms (even as datasheet view), they find it easier to just look for the query they are looking for and run it, instead of navigating to buttons or forms... Can you think of a solution on how to do otherwise?

Thank you again...

 

[dhookom]

I would train them to expect to see a 0 where there should be a zero. If they want to pretend it should be "Same Day" let them use their imagination ;-)

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

haha thanks Duane...

 

[MichaelRed]

I am distinctly to old (and cantankerous!!!!) to do this. Although I'm sure it is NOT "PC", I would just "tell" it like it is (or D... well should be?) "You hired me to do this. Let me do it properly - or get someone else!" Use the form / report to see it is "pretty print", although that will cause additional code in (read "slow down the process") the basic integrity can be maintained and you get to see the stupid results the " ... way you want it ... "



MichaelRed