[Resolved]Date plus 10 workdays

[Jefftopia]

I have a masked edit box which I am using to enter a date. Next to it I have a label.

I am trying to come up with a formula to accomplish the following:

Label1 = MaskEdBox1 + 10 workdays

Ex. If the user entered 10/7/03 in MaskEdBox1, Label1 would then show 10/21/03 (the 11th, 12th, 18th & 19th are all weekend days)

[si_the_geek]

the function you want is DateAdd.

I haven't got VB help where I am at the moment, so you'll need to check the parameters (the order of them, and the string for working day - I think its "ww").

Label1 = DateAdd("ww",MaskEdBox1, 10)

[MartinLiss]

Here's one technique that you could adapt.

Dim dteNow As Date
    Dim intWorkDays As Integer
    
    dteNow = Now
    Do Until intWorkDays = 10
        If Format(dteNow, "ddd") = "Sat" Or Format(dteNow, "ddd") = "Sun" Then
            ' Don't count it
        Else
            intWorkDays = intWorkDays + 1
        End If
        dteNow = dteNow + 1
    Loop
    
    MsgBox "10 workdays from today is " & dteNow

How will you handle holidays?

[Buzby]

"ww" is for weeks
"w" is for days
"d" is for days

As far as I know there is no easy way to work out Workdays.

Try this function:

Public Function AddWorkDays(ThisDate As Date, ThisDays As Integer) As Date
Dim tmpCount As Integer
Dim CurDate As Date
CurDate = ThisDate
tmpCount = 0
While tmpCount < ThisDays
    CurDate = DateAdd("d", 1, CurDate)
    If Weekday(CurDate) <> vbSaturday And Weekday(CurDate) <> vbSunday Then tmpCount = tmpCount + 1
Wend
AddWorkDays = CurDate
End Function

Then you can do

Msgbox AddWorkDays("13-Oct-2003",10)

which will return 27th Octob 2003.

[si_the_geek]

Originally posted by Buzby
"ww" is for weeks
"w" is for days
"d" is for days

just checked VBA help - "w" is supposed to be for weekdays, but it does exactly the same as "d"

[Jefftopia]

Thank you all.

DateAdd did not seem to take into consideration weekends when adding to the date I provided.

Seemed that MartinLiss and Buzby were both basically on the same page. Ultimately I used Buzby's piece since it was already in function form. Works great. Thank you sirs.

[MartinLiss]

That's OK, <sniff>

Seriously though, do you need to take holidays into consideration?

[Buzby]

In order to take into accounts bank holidays etc you would need to have a table of holiday dates somewhere, and check each date inside the loop

Public Function AddWorkDays(ThisDate As Date, ThisDays As Integer) As Date
Dim tmpCount As Integer
Dim CurDate As Date
CurDate = ThisDate
tmpCount = 0
While tmpCount < ThisDays
    CurDate = DateAdd("d", 1, CurDate)
    If Weekday(CurDate) <> vbSaturday And Weekday(CurDate) <> vbSunday [b]And Not IsHoliday(CurDate)[/b] Then tmpCount = tmpCount + 1
Wend
AddWorkDays = CurDate
End Function

Then you will need a IsHoliday function that takes the date and looks it up in the holidays table to decide whether that day is a holiday or not.

[Jefftopia]

Would be great to exclude holidays in the calculation. Wasn't sure if I wanted to go through the trouble at this time. If you had something I'd be happy to try it. Otherwise, I am getting pressed by the boss to get this done.

thank you sir.

[MartinLiss]

Here's an IsHoliday function. It hardcodes the dates but it would be better to read them from a database.

Public Function IsHoliday(dteDate As Date) As Boolean
 
    Select Case dteDate
        Case "12/25/03", "01/01/04" ' etc.
            IsHoliday = True
        Case Else
            IsHoliday = False
    End Select
 
End Function

[Jefftopia]

thanks again. I think I'll use your short solution now and go back at a later date to draw holiday dates from database. You are awesome.

[Jefftopia]

I ended up making a holidays table in my database and altering IsHoliday function. Here is what I came up with. Works Great. Thanks again for all the help.

Public Function IsHoliday(dteDate As Date) As Boolean
 
    Dim MySQL As String
    Dim MyDb As Database
    Dim MySet As Recordset
    
    MySQL = "SELECT * FROM tblHolidays"
    
    Set MyDb = OpenDatabase(RDSDATA_PATH & "rdstables.mdb")
    Set MySet = MyDb.OpenRecordset(MySQL)
    
    If MySet.RecordCount > 0 Then
        Do Until MySet.EOF
            If dteDate = MySet.Fields("Date") Then
                IsHoliday = True
                Exit Do
            Else
                IsHoliday = False
            End If
            MySet.MoveNext
        Loop
    End If
 
    MySet.Close
    MyDb.Close
    Set MySet = Nothing
    Set MyDb = Nothing
 
End Function

[MartinLiss]

I added vbcode tags to your post to make it more readable. That's something you should almost always do when you post code.


Don't do the SQL within the Function. Doing it that way entails a lot of overhead every time the function is called. A better solution would be to load an array with the dates at startup and have the IsHoliday function read the array. Here is untested code to do that.

Option Explicit
Private dteHolidays() As Date
 
Private Sub Form_Load()
 
    Dim MySQL As String
    Dim MyDb As Database
    Dim MySet As Recordset
    
    MySQL = "SELECT * FROM tblHolidays"
    
    Set MyDb = OpenDatabase(RDSDATA_PATH & "rdstables.mdb")
    Set MySet = MyDb.OpenRecordset(MySQL)
    
    If MySet.RecordCount > 0 Then
        ReDim dteHolidays(MySet.RecordCount)
        Do Until MySet.EOF
            dteHolidays(UBound(dteHolidays)) = MySet.Fields("Date")
            MySet.MoveNext
        Loop
    End If
 
    MySet.Close
    MyDb.Close
    Set MySet = Nothing
    Set MyDb = Nothing
 
End Sub
Public Function IsHoliday(dteDate As Date) As Boolean
 
    Dim intIndex As Integer
    
    For intIndex = 0 To UBound(dteHolidays) - 1
        If dteDate = dteHolidays(intIndex) Then
            IsHoliday = True
            Exit Function
        End If
    Next
    IsHoliday = False
 
End Function

[MartinLiss]

One other thing you should consider doing is to compare the date in question to the latest date in the array, and if the date in question is greater issue a message like "Warning. Holiday list needs to be updated".

[Jefftopia]

I like the way you are using UBound to load the array. I must admit that I have been mostly a one trick pony (not fully taken advantage of arrays). I am going to try and change that going forward. Also, liked your suggestion for the Warning message. I am going to institute that tomorrow.

PS I will start tagging my code going forward.

Thanks for your time.