WorkDay Calculation

[Guest]

I am trying to add "x" number of days to a date field. If the result ends on
a Saturday or Sunday, I need next Monday's date to be the end result. What
is the best way to achieve this?

Ginger

 

[Guest]

Hi,

Try the weekday function e.g.

dim mydate as date
mydate = WeekDay(YourDate, vbSunday) 'set the 1st day to Sunday

If mydate = 1 then YourDate = Yourdate +1

Haven't tested it, but this should take the date value (expressed as
Yourdate above, and add 1 day to it. You would need to add 2 days if the
value returned was 7 (Saturday)

 

[Guest]

Hi Paul,
Thank you for the input. I'm just beginning to experiment with functions,
so I'm trying to understand the logic behind this. Do I need a table for the
days of the week and another field that shows it's value. Such as Sunday as
the day and 1 as the value? Also, your reference, YourDate, does that need
to tie to the date field I will be using or can it be defined in order to use
it in various queries?
Ginger

 

[Douglas J. Steele]

WeekDay is a built-in function in VBA. When you pass it a date, it returns 1
if the date is a Sunday, 2 if it's a Monday and so on to 7 for Saturday. To
make it easier to remember, Access has intrinisic constants named vbSunday,
vbMonday and so on define with those numeric values. You don't need a table.

And yes, in Paul's example, you'd replace "YourDate" with whatever your
actual value is named.

 

[Guest]

I have attempted to build a Module to accomodate this function, but am
getting an error on my If stmts, I'm also not sure this will accomplish what
I'm trying to do, but this is the way it logically make sense to me. I have
attached my code:


Public Function WeekdayN(StartDate As Date, WeekdayName As Integer) As Date


'Name: WeekdayN
'Inputs: StartDate As Date
' EndDate As Date
'Returns: Date
'Author: Ginger Frye
'Date: February 17, 2006
'Comment: Adds number of days to a specific date and returns
'new date excluding weekends and holidays
'---------------------------------------------------------------------------

On Error GoTo Err_WeekdayN
Dim MyDay As Date
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("Select [HolidayDate] FROM Holidaystbl",
dbOpenShapshot)


rst.FindFirst "[HolidayDate]= #" & StartDate & "#"
If WeekdayName = 1 Then (StartDate) + 1
If WeekdayName = 7 Then (StartDate) + 2
If rst.NoMatch Then StartDate

Exit_WeekdayN:
Exit Function

Err_WeekdayN:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WeekdayN
End Select

End Function

Thank you for any input you can give me. Ginger