[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# DateAdd for business days or calendar days which ever is greater

Posted on 2008-10-13
Medium Priority
696 Views
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.

"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
Question by:dddw
[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
• 2

LVL 120

Expert Comment

ID: 22708269
define how 1 business day can be greater then 72 hours and vice versa.
0

LVL 52

Accepted Solution

Gustav Brock earned 2000 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
``````
0

Author Closing Comment

ID: 31505741
Thank you!
0

LVL 52

Expert Comment

ID: 22711551
You are welcome!

/gustav
0

## Featured Post

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
###### Suggested Courses
Course of the Month13 days, 1 hour left to enroll

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

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