Calculate Weekdays Only

  • Thread starter Thread starter Mary B. via AccessMonster.com
  • Start date Start date
M

Mary B. via AccessMonster.com

I have a form with startdate on it. I need to calculate startdate + 3 days
the time we are giving everyone to finish an item. But what if the startdate
is on Friday? I don't want to factor in the weekends. Does anyone know how
to do this??

Thanks,
Mary
 
Use like:

FinishDate = PlusWorkdays(startdate,3)

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
MinusWorkdays = DateAdd("d", 1, MinusWorkdays)
If Weekday(MinusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table
' If Weekday(dteCurrDate, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & _
dteCurrDate & "#")) Then
intNumDays = intNumDays - 1
End If
Loop
End Function

I have a form with startdate on it. I need to calculate startdate + 3 days
the time we are giving everyone to finish an item. But what if the startdate
is on Friday? I don't want to factor in the weekends. Does anyone know how
to do this??

Thanks,
Mary
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
Fixed my typo in line...RG

Use like:

FinishDate = PlusWorkdays(startdate,3)

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
Click to expand...

PlusWorkdays = DateAdd("d", 1, MinusWorkdays)
If Weekday(MinusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table
' If Weekday(dteCurrDate, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & _
dteCurrDate & "#")) Then
intNumDays = intNumDays - 1
End If
Loop
End Function

I have a form with startdate on it. I need to calculate startdate + 3 days
the time we are giving everyone to finish an item. But what if the startdate
is on Friday? I don't want to factor in the weekends. Does anyone know how
to do this??

Thanks,
Mary
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
One more time! Duhh.


Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
If Weekday(PlusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table
' If Weekday(dteCurrDate, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", _
"[HolDate] = #" & dteCurrDate & "#")) Then
intNumDays = intNumDays - 1
End If
Loop
End Function

Use like:

FinishDate = PlusWorkdays(startdate,3)

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
MinusWorkdays = DateAdd("d", 1, MinusWorkdays)
If Weekday(MinusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table
' If Weekday(dteCurrDate, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & _
dteCurrDate & "#")) Then
intNumDays = intNumDays - 1
End If
Loop
End Function

I have a form with startdate on it. I need to calculate startdate + 3 days
the time we are giving everyone to finish an item. But what if the startdate
is on Friday? I don't want to factor in the weekends. Does anyone know how
to do this??

Thanks,
Mary
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
Forgot to mention that I wrote about this in my September, 2004 "Access
Answers" column for Pinnacle Publication's "Smart Access".

You can download the column (and accompanying sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

One of the solutions there shows essentially a one-liner (okay, it's a very
long one line!) that means you don't have to loop through all of the days,
unlike a lot of the other solutions posted.
 
Back
Top