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
  • Last Modified:

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
Asked:
LeftField
1 Solution
 
shanesuebsahakarnCommented:
I'm not sure I understand what you are trying to achieve. Can't you just subtract 7 days in all circumstances ?
0
 
LeftFieldAuthor 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
 
TextReportCommented:
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
 
codeguyCommented:


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
 
LeftFieldAuthor 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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