dateadd for weekdays only

[rajeevs]

Hi
I been away for sometime. Pls help me with my new problem
i need to do a leave calculation using a text box.
I have a start date (date format) and number of days(number).
The calculation i can do is
lastdate=dateadd("d",-1,[start date]+[number of days])
this gives the difference, but i need to minus the weekends from the total
I tried using "w" instead of "d" but no use
pls help me

Rj

[missinglinq]

For obvious reasons, this is not something that can be done with a line or two of code! I picked this function up somewhere in the past. Sad to say, I don't know who to credit for it!

In the objects dialog box, click on Modules
Click on New
Copy and Paste this code in the module

'**********************************************************
'Declarations section of the module
'**********************************************************
 
Option Explicit
 
'==========================================================
' The DateAddW() function provides a workday substitute
' for DateAdd("w", number, date). This function performs
' error checking and ignores fractional Interval values.
'==========================================================
Function DateAddW (ByVal TheDate, ByVal Interval)
 
   Dim Weeks As Long, OddDays As Long, Temp As String
 
   If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _
              VarType(Interval)  > 5 Then
      DateAddW = TheDate
   ElseIf Interval = 0 Then
      DateAddW = TheDate
   ElseIf Interval > 0 Then
      Interval = Int(Interval)
 
   ' Make sure TheDate is a workday (round down).
 
      Temp = Format(TheDate, "ddd")
      If Temp = "Sun" Then
         TheDate = TheDate - 2
      ElseIf Temp = "Sat" Then
         TheDate = TheDate - 1
      End If
 
   ' Calculate Weeks and OddDays.
 
      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate + (Weeks * 7)
 
  ' Take OddDays weekend into account.
 
      If (DatePart("w", TheDate) + OddDays) > 6 Then
         TheDate = TheDate + OddDays + 2
      Else
         TheDate = TheDate + OddDays
      End If
 
      DateAddW = TheDate
Else                         ' Interval is < 0
      Interval = Int(-Interval) ' Make positive & subtract later.
 
   ' Make sure TheDate is a workday (round up).
 
      Temp = Format(TheDate, "ddd")
      If Temp = "Sun" Then
         TheDate = TheDate + 1
      ElseIf Temp = "Sat" Then
         TheDate = TheDate + 2
      End If
 
   ' Calculate Weeks and OddDays.
 
      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate - (Weeks * 7)
 
   ' Take OddDays weekend into account.
 
      If (DatePart("w", TheDate) - OddDays) > 2 Then
         TheDate = TheDate - OddDays - 2
      Else
         TheDate = TheDate - OddDays
      End If
 
      DateAddW = TheDate
    End If
 
End Function
'**************  End of Code **************

Save the module and name it AddWeekdaysOnly

Since your original code was

lastdate=dateadd("d",-1,[start date]+[number of days])

what you're actually doing is adding ([number of days] - 1) so we make that the Interval.

To invoke the function:

Interval = [number of days]-1
LastDate = DateAddW([Start Date], interval)
 

Welcome to TheScripts!

Linq ;0)>

[ADezii]

Hi
I been away for sometime. Pls help me with my new problem
i need to do a leave calculation using a text box.
I have a start date (date format) and number of days(number).
The calculation i can do is
lastdate=dateadd("d",-1,[start date]+[number of days])
this gives the difference, but i need to minus the weekends from the total
I tried using "w" instead of "d" but no use
pls help me

Rj

This will point you in the right direction:
Calculating Weekdays