Solved

Finding a future date that excludes holidays and weekends.

Posted on 2014-03-12
16
1,546 Views
Last Modified: 2014-03-14
I have a database that calculates sick time for employees. Basically I use the day they first go out and I am given the number of days they have available to them. For example, an employee may have 130 days accrued. I cannot count weekends or holidays and I have to come up with a date for when their sick time expires. I have a table with holidays in it. Currently, I am looping through every day to see whether it is a weekend or holiday. If it is, I cannot count it as part of the 130 days available. This is extremely slow as every day is analyzed. I'm sure there must be an easier way to accomplish this using a date function to determine weekdays only. I just don't know how to check for weekends and holidays together.
0
Comment
Question by:smm6809
  • 5
  • 5
  • 3
  • +2
16 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39923923
This is a high-level suggestion.
create an SQL query.
select count of dates between start and end date where
weekday (date) between 1 and 5, and date <> not(qryGetHoliday)
0
 

Author Comment

by:smm6809
ID: 39924046
Don't know the end date.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39924373
You can divide the number of available sick days by 5 to determine the number of work weeks.  Then multiply that by 2.  Add the result to the number of sick days to calculate the potential end date.  Then you have to find out if there are any holidays between the out date and the potential end date and increase the potential end date by 1 for each holiday.

That is a very rough algorithm.  You will need to work out how to handle partial weeks.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39924787
the end date is today
0
 

Author Comment

by:smm6809
ID: 39924979
Appreciate comments and have considered the last post in the past but even then every day I add will need to be checked for weekend or holiday.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39924987
hence the sql suggestion (today is today). all you are doing is subtracting the count of holidays (between 2 dates) from the count of weekdays between 2 dates.

Maybe I am mis-understanding the problem - is the above suggestion off the topic?


I got it now - you need a future date. - please disregard my jabbering :-)
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39925230
if you know how many days they have consumed to-date (using the above SQL), and how many available in total, you can calculate how many are left, using the formula
(left/5)*7 + holidays will tell you days until consumed.

you may have to use a sequential binary locate (with trial dates) above and below with diminishing range to ascertain the exact end date. i.e. while total <> remaining, reduce range

It sounds as if your problem is partially because you don't know how many holidays occur in the remaining days, because you don't know how many remaining days. (circular ref)?

use the weekday function between 1 and 5 for weekdays, and count of holidays between start and estimated end for holidays.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39925794
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39925900
HI,

pls try (tblHolidays as table for the holidays, HolDate is the field conting the date)

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
'if dteStart on a weekend correct to Monday
If Weekday(dteStart, 2) > 5 Then
dteStart = dteStart + (2 - (dteStart Mod 7))
End If
' add days with W-E
If Weekday(dteStart, 2) = 1 Then ' On Monday
    PlusWorkdays = CDate(dteStart + ((intNumDays \ 5) * 7) + intNumDays Mod 5) - 3 ' Weekends
Else
    PlusWorkdays = CDate(dteStart + ((intNumDays \ 5) * 7) + intNumDays Mod 5) - 1
End If
' Holidays on workdays until intNumDays workdays
HolidaysUntilPlusWorkdays = DCount("[HolDate]", "tblHolidays", _
    "[HolDate]>=#" & Format(dteStart, "mm\/dd\/yyyy") & _
    "# AND [HolDate]<=#" & Format(PlusWorkdays, "mm\/dd\/yyyy") & _
    "# AND Weekday([HolDate],2)<6")
'Add these holiday days
For Idx = 1 To HolidaysUntilPlusWorkdays
    If Weekday(PlusWorkdays, 2) = 5 Then
        PlusWorkdays = DateAdd("d", 3, PlusWorkdays)
    Else
        PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
    End If
    'if the temporary day is a holiday add a workday
    Do While DLookup("[HolDate]", "tblHolidays", _
            "[HolDate]=#" & Format(PlusWorkdays, "mm\/dd\/yyyy") & "#")
        If Weekday(PlusWorkdays, 2) = 5 Then
            PlusWorkdays = DateAdd("d", 3, PlusWorkdays)
        Else
            PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
        End If
    Loop
Next

End Function

Open in new window

Regards
0
 

Author Comment

by:smm6809
ID: 39926063
Sorry if I did not communicate the situation correctly. From reading posts, I know I can use the weekday function initially. So if someone's sick time ends 200 days from today, I can determine how many weekdays that is. But then, I have to reference a table to determine holidays during that period. Say there are 5. I push the date out 5 days, but then what if there is a weekend or holiday in those 5 days? I have to run something again to check the new days. I feel like it's a perpetual loop. Thoughts?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39926068
You did communicate the situation correctly, and my function does that.

/gustav
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39926110
Hi,

It's not a perpetual loop while all days are not holidays

You will only loop as much as there are holiday dates between the start date and the end date result

see my code above

Regards
0
 

Author Comment

by:smm6809
ID: 39926220
Rgonzo1971, I am so close using your code! I just have a question about:
Do While DLookup("[HolDate]", "tblHolidays", _
            "[HolDate]=#" & Format(PlusWorkdays, "mm\/dd\/yyyy") & "#")
        If Weekday(PlusWorkdays, 2) = 5 Then
            PlusWorkdays = DateAdd("d", 3, PlusWorkdays)
        Else
            PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
        End If
    Loop
Next
I am not using a table as I need to know what union the person belongs to (different unions have different holidays.) Also, never used a DLookup. I am using a SQL string as recordset. How would that work in the above code? Here is my SQL:
    strSQL = "Select * " _
        & "     from qHoliday " _
        & "    WHERE numUnionID= " & numUnion & "" _
        & "      And dtHoliday between #" & dteStart & "# and #" & PlusWorkdays & "#"

    Set rs = New ADODB.Recordset
    rs.Open strSQL, mConn
Also, what do I fill in or pass to  [HolDate] if anything? Thanks!
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39926789
hence my 'sequential binary locate ' suggestion. bang-away with diminishing range.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39926957
Hi,

pls try

DLookup("[dtHoliday]", "qHoliday", _
            "[dtHoliday]=#" & Format(PlusWorkdays, "mm\/dd\/yyyy") & "# " & _
            "[numUnionID]=" & numUnion )

Open in new window

Regards
0
 

Author Closing Comment

by:smm6809
ID: 39929438
Had to tweak a couple of things but a great help! Thank you
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question