Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77

    Exclamation Remove weekends and holiday from Date range - CODE provided

    I'm using the following code to calculate the date difference. I need help modifying it to be able to remove weekends and holidays.



    Code:
    Public Function Diff2Dates(Interval As String, Date1 As Variant, Date2 As Variant, _
    Optional ShowZero As Boolean = False) As Variant
    'Author:    ? Copyright 2001 Pacific Database Pty Limited
    '           Graham R Seach MCP MVP gseach@pacificdb.com.au
    '           Phone: +61 2 9872 9594  Fax: +61 2 9872 9593
    '           This code is freeware. Enjoy...
    '           (*) Amendments suggested by Douglas J. Steele MVP:sick:
    On Error GoTo Err_Diff2Dates
       Dim booCalcYears As Boolean
       Dim booCalcMonths As Boolean
       Dim booCalcDays As Boolean
       Dim booCalcHours As Boolean
       Dim booCalcMinutes As Boolean
       Dim booCalcSeconds As Boolean
       Dim booCalcWeeks As Boolean
       Dim booSwapped As Boolean
       Dim dtTemp As Date
       Dim intCounter As Integer
       Dim lngDiffYears As Long
       Dim lngDiffMonths As Long
       Dim lngDiffDays As Long
       Dim lngDiffHours As Long
       Dim lngDiffMinutes As Long
       Dim lngDiffSeconds As Long
       Dim lngDiffWeeks As Long
       Dim varTemp As Variant
       Const INTERVALS As String = "dmyhnsw"
    'Check that Interval contains only valid characters
       Interval = LCase$(Interval)
       For intCounter = 1 To Len(Interval)
          If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
             Exit Function
          End If
       Next intCounter
    'Check that valid dates have been entered
       If IsNull(Date1) Then Exit Function
       If IsNull(Date2) Then Exit Function
       If Not (IsDate(Date1)) Then Exit Function
       If Not (IsDate(Date2)) Then Exit Function
    'If necessary, swap the dates, to ensure that
    'Date1 is lower than Date2
       If Date1 > Date2 Then
          dtTemp = Date1
          Date1 = Date2
          Date2 = dtTemp
          booSwapped = True
       End If
       Diff2Dates = Null
       varTemp = Null
    'What intervals are supplied
       booCalcYears = (InStr(1, Interval, "y") > 0)
       booCalcMonths = (InStr(1, Interval, "m") > 0)
       booCalcDays = (InStr(1, Interval, "d") > 0)
       booCalcHours = (InStr(1, Interval, "h") > 0)
       booCalcMinutes = (InStr(1, Interval, "n") > 0)
       booCalcSeconds = (InStr(1, Interval, "s") > 0)
       booCalcWeeks = (InStr(1, Interval, "w") > 0)
    'Get the cumulative differences
       If booCalcYears Then
          lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
                  IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
          Date1 = DateAdd("yyyy", lngDiffYears, Date1)
       End If
       If booCalcMonths Then
          lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
                  IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
          Date1 = DateAdd("m", lngDiffMonths, Date1)
       End If
       If booCalcWeeks Then
          lngDiffWeeks = Abs(DateDiff("w", Date1, Date2)) - _
                  IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
          Date1 = DateAdd("ww", lngDiffWeeks, Date1)
       End If
       If booCalcDays Then
          lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
                  IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
          Date1 = DateAdd("d", lngDiffDays, Date1)
       End If
       If booCalcHours Then
          lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
                  IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
          Date1 = DateAdd("h", lngDiffHours, Date1)
       End If
       If booCalcMinutes Then
          lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
                  IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
          Date1 = DateAdd("n", lngDiffMinutes, Date1)
       End If
       If booCalcSeconds Then
          lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
          Date1 = DateAdd("s", lngDiffSeconds, Date1)
       End If
       If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
          varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " YRS", " YR")
       End If
       If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
          If booCalcMonths Then
             varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                       lngDiffMonths & IIf(lngDiffMonths <> 1, " MTHs", " MTH")
          End If
       End If
       If booCalcWeeks And (lngDiffWeeks > 0 Or ShowZero) Then
          If booCalcWeeks Then
             varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                       lngDiffWeeks & IIf(lngDiffWeeks <> 1, " WKs", " WK")
          End If
       End If
       If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
          If booCalcDays Then
             varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                       lngDiffDays & IIf(lngDiffDays <> 1, " Days", " Day")
          End If
       End If
       If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
          If booCalcHours Then
             varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                       lngDiffHours & IIf(lngDiffHours <> 1, " HRs", " HR")
          End If
       End If
       If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
          If booCalcMinutes Then
             varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                       lngDiffMinutes & IIf(lngDiffMinutes <> 1, " MINs", " MIN")
          End If
       End If
       If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
          If booCalcSeconds Then
             varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                       lngDiffSeconds & IIf(lngDiffSeconds <> 1, " SECs", " SEC")
          End If
       End If
       If booSwapped Then
          varTemp = "-" & varTemp
       End If
       Diff2Dates = Trim$(varTemp)
    End_Diff2Dates:
       Exit Function
    Err_Diff2Dates:
       Resume End_Diff2Dates
    End Function

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by orange View Post
    No this doesn't work. I need the breakdown that the code provides.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly do you need? An example would clarify requirement.
    The sample I showed deals with typical business/workdays and accommodates holidays.
    You might get some ideas from that function.

  5. #5
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by orange View Post
    What exactly do you need? An example would clarify requirement.
    The sample I showed deals with typical business/workdays and accommodates holidays.
    You might get some ideas from that function.
    Pardon the late response,

    The code I've provided works great. However, can you help add more IF statements to help remove weekends, and holidays/days off (I have a table with holidays in it) from the date range?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I'm afraid that Orange is correct. Your code here will not accommodate what you are trying to do. The only way to remove weekends and holidays is to use the function in the link to return a number of working days between two dates. You can then translate this into years or months or weeks.

    For hours or minutes or seconds, you will have to modify the function to return the number of days to be excluded, then subtract this from the total you create (days * 24 if hours, etc).

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ???The code I've provided works great.

    But it doesn't address your requirement. You want to remove weekends and holidays between certain dates.

    Also, you haven't really identified what you want --level of detail with example.

  8. #8
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by orange View Post
    ???The code I've provided works great.

    But it doesn't address your requirement. You want to remove weekends and holidays between certain dates.

    Also, you haven't really identified what you want --level of detail with example.
    Thanks for the reply.

    My goal is to compare two dates and calculate the time that is in between. Minus weekend hours and holidays. I hope this helps understand my goal. The code above does do the calculation to a certain degree. The only thing is that it doesn't subtract weekend hours and holidays from the total. Is there a way you could help point me in the right direction? Maybe, you have some code I could use?

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have a table for Holidays? You're going to need that to remove Holidays, it would seem.

    As for this -"calculate the time that is in between" - do you want hrs, min, seconds????

  10. #10
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by orange View Post
    Do you have a table for Holidays? You're going to need that to remove Holidays, it would seem.

    As for this -"calculate the time that is in between" - do you want hrs, min, seconds????
    I do have a table for holidays, TBL_Holidays, and the time format of hours, minutes and seconds (HH:NN:SS;@) is greatly appreciated.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about an example of your date1 and date2? If you are working with business days, don' they have standard start and end times? Or are you using 24 hours for the working/business day?
    What exactly is the issue? Please be specific with an example.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @BRASILO,
    My question is WHEN do you want to remove weekends and holidays between certain dates.

    For instance you could have Diff2Dates("y",#10/1/2000#,#09/30/2017#) (calculate years)
    Does it make sense to remove weekends and holidays? Not really.

    How about Diff2Dates("my",#10/1/2000#,#09/30/2017#) (calculate months and years)
    Does it make sense to remove weekends and holidays? Again, not really.

    How about Diff2Dates("myw",#10/1/2000#,#09/30/2017#) (calculate weeks, months and years)
    Does it make sense?? Nope...

    How about Diff2Dates("hn",#10/1/2000#,#09/30/2017#) (calculate hours and minutes)
    Does it make sense?? Not even close...

    The only time it makes sense to remove weekends and holidays between dates is when the interval is ONLY days and the holiday table includes the holidays for the date range.
    How about Diff2Dates("d",#6/1/2017#,#09/30/2017#) (calculate days)
    Does it make sense?? Now we're talking


    If the interval is only "d", you could add code to remove weekends and holidays between the two dates.

  13. #13
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by orange View Post
    How about an example of your date1 and date2? If you are working with business days, don' they have standard start and end times? Or are you using 24 hours for the working/business day?
    What exactly is the issue? Please be specific with an example.
    Happy Halloween!

    I see what you are asking now. Thanks for the further explanation. I'm using a 24 hour business day. So a regular 8 hour day is not applicable. For example, lets look at the following date/time range: 01:31PM 10/26/2017 - 10:00AM 11/01/2017. In this date range there is 1 holiday(10/31) and a weekend (10/28 - 10/29) that is about 72 hours that needs to be removed from the final hours/minutes/seconds calculation.

    Once again, many thanks for your much need assistance.

  14. #14
    BRASILO is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    77
    Quote Originally Posted by ssanfu View Post
    @BRASILO,
    My question is WHEN do you want to remove weekends and holidays between certain dates.

    For instance you could have Diff2Dates("y",#10/1/2000#,#09/30/2017#) (calculate years)
    Does it make sense to remove weekends and holidays? Not really.

    How about Diff2Dates("my",#10/1/2000#,#09/30/2017#) (calculate months and years)
    Does it make sense to remove weekends and holidays? Again, not really.

    How about Diff2Dates("myw",#10/1/2000#,#09/30/2017#) (calculate weeks, months and years)
    Does it make sense?? Nope...

    How about Diff2Dates("hn",#10/1/2000#,#09/30/2017#) (calculate hours and minutes)
    Does it make sense?? Not even close...

    The only time it makes sense to remove weekends and holidays between dates is when the interval is ONLY days and the holiday table includes the holidays for the date range.
    How about Diff2Dates("d",#6/1/2017#,#09/30/2017#) (calculate days)
    Does it make sense?? Now we're talking


    If the interval is only "d", you could add code to remove weekends and holidays between the two dates.
    The hour and minutes interval calculation is a better choice. However, being able to remove holidays and weekend hours from the total hours is needed. I gave orange a better explanation of what I was referring too. It's worth keeping in mind that I'm not great at utilizing expert verbiage to describe my end goal. Also, keep in mind it'll take me a few tries to clearly explain my desired outcome from my post.

  15. #15
    Join Date
    Apr 2017
    Posts
    1,673
    Here is a link with a bunch of user-defined date functions - look at p.e. CountWorkdaysA() near bottom.

    http://access.mvps.org/access/datetime/date0012.htm

    The function uses an array to keep state holidays. Unless you modify it to use Holiday's table instead, you have to fill this array for some number of years into future. And the function counts full workdays. When you want to take into account start and end times of period, then you have to calculate the time to substract separately.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Remove Holiday's and Weekend Involving Time Stamps
    By Dave_01 in forum Programming
    Replies: 8
    Last Post: 01-26-2016, 02:13 PM
  2. Replies: 3
    Last Post: 01-26-2016, 01:56 PM
  3. Date calculation excluding the weekends
    By Aosmond in forum Forms
    Replies: 1
    Last Post: 08-04-2014, 08:27 PM
  4. how do I not record date of the weekend or holiday?
    By fabiobarreto10 in forum Forms
    Replies: 4
    Last Post: 06-12-2012, 09:33 AM
  5. Code to combine report filter and date range
    By rhubarb in forum Reports
    Replies: 17
    Last Post: 10-28-2011, 03:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums