Solved

Finding a future date that excludes holidays and weekends.

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

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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 51

Expert Comment

by:Gustav Brock
ID: 39925794
0
 
LVL 52

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 51

Expert Comment

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

/gustav
0
 
LVL 52

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 52

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

615 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