Solved

Finding a future date that excludes holidays and weekends.

Posted on 2014-03-12
16
1,425 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 34

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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 48

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 48

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 48

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now