Solved

DateAdd for business days or calendar days which ever is greater

Posted on 2008-10-13
4
679 Views
Last Modified: 2013-11-28
capricorn1 was able to help with the below answer.  I have another question that is a bit more complex.  My original question was how to create a module and expression to calculate business days.  

The answer is:

"place this codes in  a module

Function getDeadLineDate(dDate As Date, Span As Integer) As Date
Dim j As Integer, i As Integer, dtStart
dtStart = dDate
For j = 1 To Span
    dtStart = dtStart + 1
    Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 '_
        dtStart = dtStart + 1
        i = i + 1
    Loop
Next
getDeadLineDate = DateAdd("d", i + Span, dDate)

End Function

to use

If [State or Federal Mandates_ID] = 4 And [Review Level_ID] = 2 Then
    [Determination Compliance Deadline D/T] = getDeadLineDate([Request D/T],2)
End If"

I have another question...  I want to use a similar expression to decide to use "which ever is greater" when it comes to business days or calendar days.  I need a formula that calculates either 1 business day or 72 hours which ever is greater.  This seems to only be a problem on Fridays.  I need it to use 1 business day if it is Friday otherwise use 72 hours.

Thanks for the help!
0
Comment
Question by:dddw
  • 2
4 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22708269
define how 1 business day can be greater then 72 hours and vice versa.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 22709150
> I need it to use 1 business day if it is Friday otherwise use 72 hours.

But if it is Thursday that would return Sunday. Probably not what you want.

Your next question will probably be how to skip a juxtaposed holiday. So, what you can do is to use the functions below like this:

datToday = Date
datNext = DateSkipNonWorkingday(DateAdd("d", 3, datToday))

This way datToday can be any weekday - including weekend - while datNext will be at least three days later skipping any weekend day or holiday.

/gustav
Public Function DateSkipNoneWorkingday( _

  ByVal datDate As Date, _

  Optional ByVal booReverse As Boolean) _

  As Date
 

' Purpose: Calculate first working day following/preceding datDate.

'

' May be freely used and distributed.

' 1999-07-03, Cactus Data ApS, CPH

  

  Dim datNext As Date

  Dim datTest As Date

    

  datNext = datDate

  Do

    datTest = datNext

    datNext = DateSkipHoliday(datTest, booReverse)

    datNext = DateSkipWeekend(datNext, booReverse)

  Loop Until DateDiff("d", datTest, datNext) = 0

  

  DateSkipNoneWorkingday = datNext
 

End Function
 

Public Function DateSkipWeekend( _

  ByVal datDate As Date, _

  Optional ByVal booReverse As Boolean) _

  As Date
 

' Purpose: Calculate first working day equal to or following/preceding datDate.

' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.

' Limitation: Does not count for public holidays.

'

' May be freely used and distributed.

' 1999-07-03, Cactus Data ApS, CPH

  

  Const cintWorkdaysOfWeek As Integer = 5
 

  Dim bytSunday   As Byte

  Dim bytWeekday  As Byte

  

  bytSunday = WeekDay(vbSunday, vbMonday)

  bytWeekday = WeekDay(datDate, vbMonday)

  

  If bytWeekday > cintWorkdaysOfWeek Then

    ' Weekend.

    If booReverse = False Then

      ' Get following workday.

      datDate = DateAdd("d", 1 + bytSunday - bytWeekday, datDate)

    Else

      ' Get preceding workday.

      datDate = DateAdd("d", cintWorkdaysOfWeek - bytWeekday, datDate)

    End If

  End If
 

  DateSkipWeekend = datDate
 

End Function
 

Public Function DateSkipHoliday( _

  ByVal datDate As Date, _

  Optional ByVal booReverse As Boolean) _

  As Date
 

' Purpose: Calculate first day following/preceding datDate if this is holiday.

'

' May be freely used and distributed.

' 1999-07-03, Cactus Data ApS, CPH

  

  ' Adjust to fit your table of holidays.

  Const cstrHolidayTable  As String = "tblHoliday"

  Const cstrHolidayField  As String = "HolidayDate"

  

  While Not IsNull(DLookup(cstrHolidayField, cstrHolidayTable, cstrHolidayField & " = " & Format(datDate, "\#m\/d\/yyyy\#")))

    datDate = DateAdd("d", 1 - Abs(2 * booReverse), datDate)

  Wend
 

  DateSkipHoliday = datDate
 

End Function

Open in new window

0
 

Author Closing Comment

by:dddw
ID: 31505741
Thank you!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 22711551
You are welcome!

/gustav
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now