Solved

Finding a future date that excludes holidays and weekends.

Posted on 2014-03-12
16
1,630 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 37

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 50

Expert Comment

by:Gustav Brock
ID: 39925794
0
 
LVL 51

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 50

Expert Comment

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

/gustav
0
 
LVL 51

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 51

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

729 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