Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 569

# 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
0
LeftField
1 Solution

Commented:
I'm not sure I understand what you are trying to achieve. Can't you just subtract 7 days in all circumstances ?
0

Author Commented:
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.
0

Commented:
LeftField I use the code below that allows you to keep a list of dates in a table so it can also exclude holidays.

Cheers, Andrew

The Tables is USystblBankHolidays
BHDate DateTime
BHDesc Text

The Query is USysqryBankHolidays
SELECT USystblBankHolidays.*
FROM USystblBankHolidays
WITH OWNERACCESS OPTION;

The Query is USysqryBankHolidayCount
PARAMETERS pdteStartDate DateTime, pdteEndDate DateTime;
SELECT Count(USystblBankHolidays.BHDate) AS QTY
FROM USystblBankHolidays
WHERE (((USystblBankHolidays.BHDate) Between [pdteStartDate] And [pdteEndDate]))
WITH OWNERACCESS OPTION;

Function MKC_LIB_NetWorkDays(BegDate As Variant, EndDate As Variant, Optional pbooIgnoreHolidays As Boolean) As Integer
Dim db As Database
Dim qd As QueryDef
Dim rst As Recordset

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
Dim Holidays As Long

BegDate = Int(CDate(BegDate))
EndDate = Int(CDate(EndDate))

If pbooIgnoreHolidays Then
Holidays = 0
Else
Set db = CurrentDb()
Set qd = db.QueryDefs("USysqryBankHolidayCount")
qd.Parameters!pdteStartDate = BegDate 'Format(BegDate, "dd/mmmm/yyyy")
qd.Parameters!pdteEndDate = EndDate 'Format(EndDate, "dd/mmmm/yyyy")
Set rst = qd.OpenRecordset()
Holidays = rst!qty
rst.Close
End If

WholeWeeks = DateDiff("w", BegDate, EndDate)
EndDays = 0

Do While DateCnt <= EndDate
If Format(DateCnt, "ddd") <> "Sun" And Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If

Loop

MKC_LIB_NetWorkDays = WholeWeeks * 5 + EndDays - Holidays

End Function
0

Commented:

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
ElseIf Interval = 0 Then
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

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

End If

End Function

0

Author Commented:
I think either of the two responses would have worked equally.  In general I was looking for alternative methods as a learning exersize.  Thanks
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.