?
Solved

Date calculation without Weekends

Posted on 2003-02-25
5
Medium Priority
?
566 Views
Last Modified: 2012-08-13
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
Comment
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
  • Learn & ask questions
5 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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

by:LeftField
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

by:
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)
    DateCnt = DateAdd("ww", WholeWeeks, BegDate)
    EndDays = 0

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

    MKC_LIB_NetWorkDays = WholeWeeks * 5 + EndDays - Holidays

End Function
0
 

Expert Comment

by:codeguy
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
        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
         
0
 

Author Comment

by:LeftField
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question