LeftField
asked on
Date calculation without Weekends
I have a field that is a date(Mydate). I need to get a new date by subtracting 5 days from MyDate and not count Weekends in the calculation.
Ex.
2/25/03 - 5 days = 2/18/2003
Ex.
2/25/03 - 5 days = 2/18/2003
shanesuebsahakarn
I'm not sure I understand what you are trying to achieve. Can't you just subtract 7 days in all circumstances ?
ASKER
Initially that is what I thought too. In my example "MyDate" can fall on any day of the week. Therefore if Mydate falls on Saturday or Sunday then you would only subtract 5 days. I realize I could do an iif statement where I first find out what day of the week it is then perform whatever calculation needs made, I was just curious if there was a cleaner way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From: http://support.microsoft.com/default.aspx?scid=kb;en-us;115489
Let me know if you need any example of this function in action.
codeguy
'=========================
' 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
ASKER
I think either of the two responses would have worked equally. In general I was looking for alternative methods as a learning exersize. Thanks