Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

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

iuianj07 (Programmer) (OP)
23 Feb 11 16:03
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

CODE

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

qryDateSent

CODE

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...

CODE

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

CODE

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...
Helpful Member!  MichaelRed (Programmer)
23 Feb 11 17:14
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 (Programmer)
23 Feb 11 17:33
smedvid (MIS)
24 Feb 11 8:35
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 (Programmer)
24 Feb 11 13:19
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 (Programmer) (OP)
24 Feb 11 13:32
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:

CODE

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:

CODE

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 (Programmer) (OP)
24 Feb 11 13:40
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 (Programmer) (OP)
24 Feb 11 13:43
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 (Programmer)
24 Feb 11 14:10
I just meant latest updates in case it was a bug
iuianj07 (Programmer) (OP)
24 Feb 11 14:15
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
Helpful Member!  dhookom (Programmer)
24 Feb 11 14:17
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:

  CLng(Now()) = 40599
  CLng(Date()) = 40598

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 (Programmer)
24 Feb 11 14:35
also add this to the module

penddte = DateValue(penddte)
pstartdte = DateValue(pstartdte)
iuianj07 (Programmer) (OP)
24 Feb 11 14:38
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 (Programmer)
24 Feb 11 15:32
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 (Programmer) (OP)
24 Feb 11 16:59
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 (Programmer)
24 Feb 11 18:00
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 winky smile

Duane
Hook'D on Access
MS Access MVP

iuianj07 (Programmer) (OP)
25 Feb 11 10:53
haha thanks Duane... :)
MichaelRed (Programmer)
25 Feb 11 11:09
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


 

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close