Solved

Date calculation without Weekends

Posted on 2003-02-25
Medium Priority
566 Views
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
Question by:LeftField
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 41

Expert Comment

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

Author Comment

ID: 8020505
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

LVL 28

Accepted Solution

TextReport earned 600 total points
ID: 8020620
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

Expert Comment

ID: 8020816

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 Comment

ID: 8025514
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month14 days, 10 hours left to enroll