Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Finding a future date that excludes holidays and weekends.

Posted on 2014-03-12
16
Medium Priority
?
1,792 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 40

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 52

Expert Comment

by:Gustav Brock
ID: 39925794
0
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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 52

Expert Comment

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

/gustav
0
 
LVL 53

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 53

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

885 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