Last Work Day Problem

[schenel]

I need to get the date for Friday of the next week.

For example today is Wednesday, July 30, 2003. I enter this into a textbox then click a button that will return Friday, August 8, 2003.

How do you do the calculation for this?

Thanks,

 

[pdx_man]

Here's a basic solution to get you started:

Private Sub Text2_BeforeUpdate(Cancel As Integer)
Dim holdtemp As Date

    holdtemp = DateAdd("ww", 1, Me.Text2)
    Do Until WeekDay(holdtemp) = vbFriday
        holdtemp = DateAdd("d", 1, holdtemp)
    Loop
    Me.Text4 = holdtemp
    
End Sub

 

[Daxton A.]

The way i see it

You would first check to see what Letter day was inputted. Then if it is say Wednesday, you would add 2 to the serial date to make it friday. Then you can add 7 to it to make it next friday.

You would do something like this:

DIM NumToAdd as integer

SELECT CASE cboDayOfWeek.text
case "Friday"
NumToAdd = 0
case "Thursday"
NumToAdd = 1
case "Wednesday"
NumToAdd = 2
case "Tuesday"
NumToAdd = 3
case "Monday"
NumToAdd = 4
case "Sunday"
NumToAdd = 5
case "Saturday"
NumToAdd = 6
END SELECT

txtTargetDate.text = DATEVALUE(UrDate) + NumToAdd + 7

(Then convert txtTargetDate.Text from a serial Date to a Regular Date Format)

May or May not work but thats how i would start.
HOPE THIS HELPS!

 

[Mile-O]

This function is simpler and has further functionality in that it can work for any day of the week depending on the arguments sent.

Public Function NextDay(ByVal dteBase As Date, _
    ByVal intDay As Integer) As Date

    If intDay < 1 Or intDay > 7 Then Exit Function
    
    NextDay = (dteBase + 7) + (8 - Weekday(dteBase, intDay))

End Function

So, to call it you would use send the date you wish to calculate from and the day number (an VB constant) represented thusly:

1. Sunday
2. Monday
3. Tuesday
4. Wednesday
5. Thursday
6. Friday
7. Saturday



So, for the date of next Friday from your given example:

MsgBox NextDay(Date, 6)

 

[Travis]

Or

Public Function NextFriday(ByRef dDate) As Date

If Weekday(dDate, vbSunday) < vbFriday Then
    NextFriday = DateAdd("d", 6 - Weekday(dDate, vbSunday), dDate)
Else
    NextFriday = DateAdd("d", 7 + (6 - Weekday(dDate, vbSunday)), dDate)
End If

End Function

 

[schenel]

Mile-O-Phile,
Travis,
pdx_man,
Daxton A.

Thank you all for your replies, this board truly shows--

"thars'-mor-n-one-way-ta-skin-a-cat!"



I really appreciate your help, hope I can be of service on this board sometime.