Finding a future date that excludes holidays and weekends.

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)

Don't know the end date.

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.

the end date is today

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.

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 :-)

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.

Here is how:

https://www.experts-exchange.com/questions/27859518/VBA-for-calculation-of-dates-and-showing-a-warning-etc.html?anchorAnswerId=38384006#a38384006

/gustav

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?

You did communicate the situation correctly, and my function does that.

/gustav

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

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!

hence my 'sequential binary locate ' suggestion. bang-away with diminishing range.

Hi,

pls try

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

Select allOpen in new window

Regards

Had to tweak a couple of things but a great help! Thank you