Problem with working days

J

JillC

How can I ensure that if I add a number of days to a date (which is a working
date) the result will also be a work date
 
D

dmoney

something like this should do the trick

Dim mydate As String
mydate = Format(Date + 2, "dddd")
If mydate = "Saturday" Then mydate = Format(Date + 4, "dddd")
If mydate = "Sunday" Then mydate = Format(Date + 4, "dddd")
 
B

Bernard Liengme

In A2 I have the start date, in A3 the number of days to add
This formula add 1 to the answer if the result of A2+A3 would be Sunday, and
2 if the result would be Saturday. Holidays add extra difficulty!
(A2+A3) +2*(WEEKDAY(A2+A3)=7)+1*(WEEKDAY(A2+A3)=1)
best wishes
 
R

Rick Rothstein

Give the following function a try...

Function AddWorkDays(StartDate As Date, WorkDays As Long) As Date
If WorkDays < 0 Then Exit Function
AddWorkDays = DateAdd("d", 7 * (WorkDays \ 5) + (WorkDays Mod 5) - _
2 * ((WorkDays Mod 5) > Abs(5 + - _
Weekday(StartDate, vbMonday))) + _
Weekday(StartDate, vbSaturday) * _
(Weekday(StartDate, vbSaturday) < 3), StartDate)
End Function

As written, this function will only *add* workdays to the StartDate. If the
WorkDays value is less than 0, then "day zero" (12/30/1899) is returned and
can be used for error checking purposes.
 
Top