Adding weekdays to selected date

[Greg Ripper]

I have a form with worker name, start date, days assigned, and end date.
The start date is entered manually. The days assigned is a drop list ranging
from 1 to 20 days.

How do I get the end date to add the drop list number of days to the start
date and have only weekdays be counted?

EG. Start date is today, combo list is 7, therefore the end date is next
tuesday?

Ripper

 

[Tom Stoddard]

Try this:

Create the following function:

Public Function AddWeekdays(intDays As Integer)
Dim intWeekends, intToday As Integer

intToday = DatePart("w", Date, vbMonday)

If intDays + intToday > 5 Then
intWeekends = 1 + ((intDays - (6 - intToday))) \ 5
Else
intWeekends = 0
End If

AddWeekdays = DateAdd("d", intDays + (2 * intWeekends), Date)

End Function


This formula will add weekdays to today's date not including today. Your
example indicates that you want to include today as one of the days. To do
that you must subtract one from intToday before doing any of the
calculations.

To use the function simply call it like this:

EndDate = AddWeekdays(ComboList)

 

[Tom Stoddard]

If there's a chance that the date you're adding days to is going to be a
Saturday or Sunday then you will need to test for that as well. Add the
following lines to the beginning of the function:

If intToday > 5 Then
If intToday = 6 Then
StartDate = StartDate + 1
End If
intToday = 0
End If