Results 1 to 8 of 8
  1. #1
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100

    How to find the number of weekends and holidays between two dates?

    Hello - I have a table with Start_Date and End_Date fields. I will be calculating the difference between them but would like to know if any of the dates between them are a weekend and or a holiday.



    Query1 tells me how many weekend days are between the Start_Date and End_Date which is 1 of the 2 things I need. How could I account for the holiday (if any)? In my query the dates 5/24/2022-5/31/2022 and 5/27/2022-5/31/2022 fall between a weekend and a holiday so I would like a 3 as a result (Sat. & Sun =2 + holiday 1).
    Attached Files Attached Files

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    plenty of examples on this and other forums - see the bottom of this thread for at least 3 examples

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Holidays are regional, so AFAIK the only way to incorporate them is to create a table that contains the holiday dates. This is something you'll have to maintain since not all holidays fall on the same date. For those, you would need a record for each individual holiday date that moves. Once you have this table, you can bring it into your query
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    I don't think we have examples that can tell me if two dates fall between a weekend or holiday. I looked around and didn't see them - if you could provide links that would be great.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Have you looked in similar threads? That is what it is there for?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Here's a way. Add this function:

    Code:
    Public Function fcnCountHolidays(dStart As Date, dEnd As Date) As Long
        Dim rsHol As DAO.Recordset
        Set rsHol = CurrentDb.OpenRecordset("Holidays", dbOpenSnapshot)
        rsHol.MoveLast: rsHol.MoveFirst
            With rsHol
                Do Until .EOF
                    If !Holiday >= dStart And !Holiday <= dEnd Then
                        fcnCountHolidays = fcnCountHolidays + 1
                    End If
                .MoveNext
                Loop
            End With
        Set rsHol = Nothing
    End Function
    Modify the query

    Click image for larger version. 

Name:	Holiday.png 
Views:	14 
Size:	12.2 KB 
ID:	47864

  7. #7
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    Thank you Davegri - exactly what I needed.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Happy to help - and thanks for the star!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-22-2020, 03:53 AM
  2. Replies: 36
    Last Post: 05-10-2018, 02:02 PM
  3. Exclude Weekends and Holidays
    By Oxygen Potassium in forum Access
    Replies: 7
    Last Post: 05-25-2016, 10:57 AM
  4. Replies: 1
    Last Post: 04-20-2015, 04:17 PM
  5. How to analyze holidays/weekends
    By Galio83 in forum Queries
    Replies: 0
    Last Post: 03-31-2011, 12:27 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