Adding Business Days only

[TotallyConfused]

Can someone please help how to revise the following by adding only 7
"Business Days".

The following is in my form field date on Click Event:

Private Sub 1stNotice_Click()
Me.1stNotice= Date
Me.Noticedue = DateAdd("w", 7, [1stNotice])
End Sub

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

Thank you in advance for any help you can provide.

 

[John W. Vinson]

Can someone please help how to revise the following by adding only 7
"Business Days".

There's nothing builtin to Access to calculate work days. You'll need some VBA
code and probably a table of holidays as well. See

http://www.mvps.org/access/datetime/date0012.htm

 

[TotallyConfused]

Is there may some sql that would work?

There's nothing builtin to Access to calculate work days. You'll need some VBA
code and probably a table of holidays as well. See

http://www.mvps.org/access/datetime/date0012.htm

 

[John W. Vinson]

Is there may some sql that would work?

You could create a table containing all the workdays for the next five or ten
years (not all that hard, actually) and join it to your query, and count
records in that table rather than using any sort of DateDiff.

 

[TotallyConfused]

Thanks for your response. However, I don't have much time to spend on this.
As ususal this is asap. I thinking how about if I modify this by using Long
date and then having a If statement to rule out Satuday and Sunday. How
would I write an IF statement to read: If Date = Friday than add 6 days.

Thank you for any help you can provide.


Private Sub 1stNotice_Click()
Me.1stNotice= Date
Me.Noticedue = DateAdd("w", 7, [1stNotice])
End Sub

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

 

[Harold Druss]

Thanks for your response. However, I don't have much time to spend on
this.
As ususal this is asap. I thinking how about if I modify this by using
Long
date and then having a If statement to rule out Satuday and Sunday. How
would I write an IF statement to read: If Date = Friday than add 6 days.

Thank you for any help you can provide.


Private Sub 1stNotice_Click()
Me.1stNotice= Date
Me.Noticedue = DateAdd("w", 7, [1stNotice])
End Sub

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

You could create a table containing all the workdays for the next five or
ten
years (not all that hard, actually) and join it to your query, and count
records in that table rather than using any sort of DateDiff.

Hi
I'm not an Access user, but here is an idea.

**************************************
Function GetDate(s As Date) As Date
Dim iCount As Long

Do While iCount < 6
Select Case Weekday(s)
Case 2 To 6
s = s + 1
iCount = iCount + 1
Case Else
s = s + 1
End Select
Loop

GetDate = s

End Function
**************************************

But as John said, you need to include holidays somehow.

Good luck
Harold

 

[TotallyConfused]

Thank you However I found this in my search for something very simple. Need
help reading this. This was in answer to a very similar question on this
Discussion Group. The user was asking for a formula and he was given this
formula. He responded that it worked. What is dtm and where would this
formula be entered? Any help will be greatly appreciated. Thank you.

dtmNextWeekyDay = IIF(Weekday(dtmEndDate) = 6,
DateAdd("d",3,dtmEndDate),DateAdd("d",1,dtmEndDate))

Thanks for your response. However, I don't have much time to spend on
this.
As ususal this is asap. I thinking how about if I modify this by using
Long
date and then having a If statement to rule out Satuday and Sunday. How
would I write an IF statement to read: If Date = Friday than add 6 days.

Thank you for any help you can provide.


Private Sub 1stNotice_Click()
Me.1stNotice= Date
Me.Noticedue = DateAdd("w", 7, [1stNotice])
End Sub

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

On Fri, 14 Mar 2008 17:22:01 -0700, TotallyConfused

Is there may some sql that would work?

You could create a table containing all the workdays for the next five or
ten
years (not all that hard, actually) and join it to your query, and count
records in that table rather than using any sort of DateDiff.

Hi
I'm not an Access user, but here is an idea.

**************************************
Function GetDate(s As Date) As Date
Dim iCount As Long

Do While iCount < 6
Select Case Weekday(s)
Case 2 To 6
s = s + 1
iCount = iCount + 1
Case Else
s = s + 1
End Select
Loop

GetDate = s

End Function
**************************************

But as John said, you need to include holidays somehow.

Good luck
Harold

 

[John W. Vinson]

Thanks for your response. However, I don't have much time to spend on this.
As ususal this is asap. I thinking how about if I modify this by using Long
date and then having a If statement to rule out Satuday and Sunday. How
would I write an IF statement to read: If Date = Friday than add 6 days.

Thank you for any help you can provide.


Private Sub 1stNotice_Click()
Me.1stNotice= Date
Me.Noticedue = DateAdd("w", 7, [1stNotice])
End Sub

Now I don't understand. You want to add seven business days unless it's
Friday, in which case you want to add 6??? You had two examples, adding "w", 7
and "w", 3. Confusingly, the "w" dateadd operand works exactly the same as
"d" - it just adds days.

The date format is absolutely irrelevant. Dates are just numbers! The format
controls how it's displayed, not what's used in the calculation.

If you want to add 7 weekdays, I calculate that you need to add 9 days if
you're starting Monday through Wednesday (skipping one weekend) and 11 days if
it's Thursday or Friday; so you could use

Me!NoticeDate = DateAdd("d", IIF(Weekday([1stNotice]) < 5, 9, 11),
[1stNotice])

Weekday returns 1 for Sunday, 2 for Monday,..., 5 for Thursday, 7 for
Saturday; you use the IIF function to choose between adding 9 or 11 as needed.
Tweak to fit your actual business rule which I don't quite understand given
your examples.

 

[TotallyConfused]

Thank you However I found this in my search for something very simple. Need
help reading this. This was in answer to a very similar question on this
Discussion Group. The user was asking for a formula and he was given this
formula. He responded that it worked. What is dtm and where would this
formula be entered? Any help will be greatly appreciated. Thank you.

dtmNextWeekyDay = IIF(Weekday(dtmEndDate) = 6,
DateAdd("d",3,dtmEndDate),DateAdd("d",1,dtmEndDate))

Thanks for your response. However, I don't have much time to spend on this.
As ususal this is asap. I thinking how about if I modify this by using Long
date and then having a If statement to rule out Satuday and Sunday. How
would I write an IF statement to read: If Date = Friday than add 6 days.

Thank you for any help you can provide.


Private Sub 1stNotice_Click()
Me.1stNotice= Date
Me.Noticedue = DateAdd("w", 7, [1stNotice])
End Sub

Now I don't understand. You want to add seven business days unless it's
Friday, in which case you want to add 6??? You had two examples, adding "w", 7
and "w", 3. Confusingly, the "w" dateadd operand works exactly the same as
"d" - it just adds days.

The date format is absolutely irrelevant. Dates are just numbers! The format
controls how it's displayed, not what's used in the calculation.

If you want to add 7 weekdays, I calculate that you need to add 9 days if
you're starting Monday through Wednesday (skipping one weekend) and 11 days if
it's Thursday or Friday; so you could use

Me!NoticeDate = DateAdd("d", IIF(Weekday([1stNotice]) < 5, 9, 11),
[1stNotice])

Weekday returns 1 for Sunday, 2 for Monday,..., 5 for Thursday, 7 for
Saturday; you use the IIF function to choose between adding 9 or 11 as needed.
Tweak to fit your actual business rule which I don't quite understand given
your examples.

 

[raskew via AccessMonster.com]

Hi -
The following will add or subtract business days from a given day.
Copy/paste to a standard module and call the function as shown:

Function UpBusDays3(pstart As Date, _
pNum As Integer, _
Optional pAdd As Boolean = True) As Date
'*******************************************
'Purpose: Add or subtract business days
' from a date
'Coded by: raskew
'Inputs: 1) +) ? UpBusDays3(#2/17/06#, 3, True)
' 2) -) ? UpBusDays3(#2/22/06#, 3, False)
'Output: 1) +) 2/22/06
' 2) -) 2/17/06
'*******************************************

Dim DteHold As Date
Dim i As Integer
Dim n As Integer

DteHold = pstart
n = pNum
For i = 1 To n
If pAdd Then 'add days
DteHold = DteHold + IIf(WeekDay(DteHold) > 5, 9 - WeekDay(DteHold),
1)
Else 'subtract days
DteHold = DteHold - IIf(WeekDay(DteHold) < 3, Choose(WeekDay
(DteHold), 2, 3), 1)
End If
Next i
UpBusDays3 = DteHold

End Function

HTH - Bob

Thank you However I found this in my search for something very simple. Need
help reading this. This was in answer to a very similar question on this
Discussion Group. The user was asking for a formula and he was given this
formula. He responded that it worked. What is dtm and where would this
formula be entered? Any help will be greatly appreciated. Thank you.

dtmNextWeekyDay = IIF(Weekday(dtmEndDate) = 6,
DateAdd("d",3,dtmEndDate),DateAdd("d",1,dtmEndDate))
[quoted text clipped - 27 lines]

Tweak to fit your actual business rule which I don't quite understand given
your examples.

 

[Arvin Meyer [MVP]]

The following has been working for me for about 10 years:

http://www.datastrat.com/Code/GetBusinessDay.txt

 

[TotallyConfused]

Thank you for your response. It seems your function looks at a Holiday
table. What would this table consist of? Just Dates? I am sorry for the
questions but I new to code and am trying to understand and apply. Sorry if
I sound anal but I am trying to incorporate something simple to apply to what
I already have. I don't have much time in fixing this. I have two instances
where I need to add business days. The first is a form. The second is a
report. How do I incorporate your function. I know to save it to Module,
how do I call it from either of my samples below. Thank you in advance for
your help.

The following is in my subform field 1stNotice, Event Click:
Private Sub 1stNotice_Click()

Me.1stNotice= Date
Me.Noticedue = DateAdd("w", 7, [1stNotice])
End Sub

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

The following has been working for me for about 10 years:

http://www.datastrat.com/Code/GetBusinessDay.txt
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Can someone please help how to revise the following by adding only 7
"Business Days".

The following is in my form field date on Click Event:

Private Sub 1stNotice_Click()
Me.1stNotice= Date
Me.Noticedue = DateAdd("w", 7, [1stNotice])
End Sub

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

Thank you in advance for any help you can provide.

 

[TotallyConfused]

Thank you for your response. Does your function look at a table? Some of
the responses I have gotten look at tables for their function. I rather not
have to look at a date table. Do not have any idea how to go about. I am
sorry for the questions but I new to code and am trying to understand and
apply. Sorry if I sound anal but I am trying to incorporate something simple
to apply to what I already have. I don't have much time in fixing this. I
have two instances where I need to add business days. The first is a form.
The second is a
report. How do I incorporate your function. I know to save it to Module,
how do I call it from either of my samples below. Thank you in advance for
your help.

The following is in my subform field 1stNotice, Event Click:
Private Sub 1stNotice_Click()
Me.1stNotice= Date
Me.Noticedue = DateAdd("w", 7, [1stNotice])
End Sub

The following is in my report field date controls:

Daysent =Now
Daydue=DateAdd("w",3,Now())

Hi -
The following will add or subtract business days from a given day.
Copy/paste to a standard module and call the function as shown:

Function UpBusDays3(pstart As Date, _
pNum As Integer, _
Optional pAdd As Boolean = True) As Date
'*******************************************
'Purpose: Add or subtract business days
' from a date
'Coded by: raskew
'Inputs: 1) +) ? UpBusDays3(#2/17/06#, 3, True)
' 2) -) ? UpBusDays3(#2/22/06#, 3, False)
'Output: 1) +) 2/22/06
' 2) -) 2/17/06
'*******************************************

Dim DteHold As Date
Dim i As Integer
Dim n As Integer

DteHold = pstart
n = pNum
For i = 1 To n
If pAdd Then 'add days
DteHold = DteHold + IIf(WeekDay(DteHold) > 5, 9 - WeekDay(DteHold),
1)
Else 'subtract days
DteHold = DteHold - IIf(WeekDay(DteHold) < 3, Choose(WeekDay
(DteHold), 2, 3), 1)
End If
Next i
UpBusDays3 = DteHold

End Function

HTH - Bob

Thank you However I found this in my search for something very simple. Need
help reading this. This was in answer to a very similar question on this
Discussion Group. The user was asking for a formula and he was given this
formula. He responded that it worked. What is dtm and where would this
formula be entered? Any help will be greatly appreciated. Thank you.

dtmNextWeekyDay = IIF(Weekday(dtmEndDate) = 6,
DateAdd("d",3,dtmEndDate),DateAdd("d",1,dtmEndDate))

Thanks for your response. However, I don't have much time to spend on this.
As ususal this is asap. I thinking how about if I modify this by using Long

[quoted text clipped - 27 lines]

Tweak to fit your actual business rule which I don't quite understand given
your examples.

 

[John W. Vinson]

Thank you However I found this in my search for something very simple. Need
help reading this. This was in answer to a very similar question on this
Discussion Group. The user was asking for a formula and he was given this
formula. He responded that it worked. What is dtm and where would this
formula be entered? Any help will be greatly appreciated. Thank you.

dtmNextWeekyDay = IIF(Weekday(dtmEndDate) = 6,
DateAdd("d",3,dtmEndDate),DateAdd("d",1,dtmEndDate))

dtm is just part of the naming convention that the person who posted this
chose to use. Access does not interpret the dtm at all - it could be
xyzNextWeeklyDay or anything else. It's JUST A NAME for a VBA variable.

I presume that this line of code was included in something else, but since you
chose not to post the rest of the code nor to answer my questions (repeated
below) I'm at a loss to help further.

And here's my suggestion which will answer your question - IF I'm interpreting
your question correctly, which you have not clarified. Did you perchance try
this?

If you want to add 7 weekdays, I calculate that you need to add 9 days if
you're starting Monday through Wednesday (skipping one weekend) and 11 days if
it's Thursday or Friday; so you could use

Me!NoticeDate = DateAdd("d", IIF(Weekday([1stNotice]) < 5, 9, 11),
[1stNotice])

 

[TotallyConfused]

Thank you John for responding. I am sorry if I have confused you and not
explained completely. My first example is from a form where user enter date
in 1stNotice field and 1stNoticedue fields gets filled by adding 3 days from
1stNotice date. This is an event in my form for 1stNotice field.

Private Sub 1stNotice_Click()
Me.IstNotice = Date
Me.Noticedue = DateAdd("w",7,[1stNotice])

End Sub

My second example is for separate Report where I have similar issue. But it
is not in an event it is in the control source for textboxes. In this
report, I have one text box that enter Now(). In my second text box I add 3
days to the Now(). However, I need workdays/business days only. So if it is
Wednesday, Thursday or Friday, I need to somehow skip Saturday and Sunday.

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

Hope this makes better sense. I hope that you will still be able to help.
Thank you very much for your time and patience.

Thank you However I found this in my search for something very simple. Need
help reading this. This was in answer to a very similar question on this
Discussion Group. The user was asking for a formula and he was given this
formula. He responded that it worked. What is dtm and where would this
formula be entered? Any help will be greatly appreciated. Thank you.

dtmNextWeekyDay = IIF(Weekday(dtmEndDate) = 6,
DateAdd("d",3,dtmEndDate),DateAdd("d",1,dtmEndDate))

dtm is just part of the naming convention that the person who posted this
chose to use. Access does not interpret the dtm at all - it could be
xyzNextWeeklyDay or anything else. It's JUST A NAME for a VBA variable.

I presume that this line of code was included in something else, but since you
chose not to post the rest of the code nor to answer my questions (repeated
below) I'm at a loss to help further.

And here's my suggestion which will answer your question - IF I'm interpreting
your question correctly, which you have not clarified. Did you perchance try
this?

If you want to add 7 weekdays, I calculate that you need to add 9 days if
you're starting Monday through Wednesday (skipping one weekend) and 11 days if
it's Thursday or Friday; so you could use

Me!NoticeDate = DateAdd("d", IIF(Weekday([1stNotice]) < 5, 9, 11),
[1stNotice])

 

[John W. Vinson]

Thank you John for responding. I am sorry if I have confused you and not
explained completely. My first example is from a form where user enter date
in 1stNotice field and 1stNoticedue fields gets filled by adding 3 days from
1stNotice date. This is an event in my form for 1stNotice field.

Private Sub 1stNotice_Click()
Me.IstNotice = Date
Me.Noticedue = DateAdd("w",7,[1stNotice])

End Sub

This code does not in the least resemble what you describe.

What you describe is "add 3 days".

Your code adds seven days.

Do you want to add three days? or do you want to add seven days?

My second example is for separate Report where I have similar issue. But it
is not in an event it is in the control source for textboxes.

The expression will be the same - just prefix it with an = sign for a control
source.

In this
report, I have one text box that enter Now(). In my second text box I add 3
days to the Now().

Now() does not return today's date. it returns today's date AND TIME, accurate
to a few microseconds. If you don't need the time portion, use Date() instead
of Now().

However, I need workdays/business days only. So if it is
Wednesday, Thursday or Friday, I need to somehow skip Saturday and Sunday.

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

AGAIN: I explained how to do this. You evidently didn't understand it or
didn't try it. Let's try again.

Daysent = Date ' to get today's date rather than #3/16/2008 17:45:21#
DayDue = DateAdd("d", IIF(Weekday(Daysent) > 3, 5, 3), DaySent)

This will check the Weekday of the value in Daysent; if it is 4, 5, 6, 7 -
Wednesday, Thursday, Friday or Saturday - it will add 5 days; if it is 3 or
less (Sunday, Monday, Tuesday) it will add 3 days.

 

[TotallyConfused]

7 Seven days for 1st Notice in my form. 3 days for my report. Thank you.

Thank you John for responding. I am sorry if I have confused you and not
explained completely. My first example is from a form where user enter date
in 1stNotice field and 1stNoticedue fields gets filled by adding 3 days from
1stNotice date. This is an event in my form for 1stNotice field.

Private Sub 1stNotice_Click()
Me.IstNotice = Date
Me.Noticedue = DateAdd("w",7,[1stNotice])

End Sub

This code does not in the least resemble what you describe.

What you describe is "add 3 days".

Your code adds seven days.

Do you want to add three days? or do you want to add seven days?

My second example is for separate Report where I have similar issue. But it
is not in an event it is in the control source for textboxes.

The expression will be the same - just prefix it with an = sign for a control
source.

In this
report, I have one text box that enter Now(). In my second text box I add 3
days to the Now().

Now() does not return today's date. it returns today's date AND TIME, accurate
to a few microseconds. If you don't need the time portion, use Date() instead
of Now().

However, I need workdays/business days only. So if it is
Wednesday, Thursday or Friday, I need to somehow skip Saturday and Sunday.

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

AGAIN: I explained how to do this. You evidently didn't understand it or
didn't try it. Let's try again.

Daysent = Date ' to get today's date rather than #3/16/2008 17:45:21#
DayDue = DateAdd("d", IIF(Weekday(Daysent) > 3, 5, 3), DaySent)

This will check the Weekday of the value in Daysent; if it is 4, 5, 6, 7 -
Wednesday, Thursday, Friday or Saturday - it will add 5 days; if it is 3 or
less (Sunday, Monday, Tuesday) it will add 3 days.

Hope this makes better sense. I hope that you will still be able to help.
Thank you very much for your time and patience.

 

[TotallyConfused]

Mr. Vinson, I finally got a chance to go over everything calmly. Apply it
and test it. Thank you so much for your expertise, patience and for this
forum. It worked!!! Just what I needed to complete this db. Instead of
giving up on Access it only makes me more determine to keep learning and
applying all that this forum has to teach and offer.

Thank you all very much for your responses. I most definitely will use,
test and apply your examples when needed.

Thank you John for responding. I am sorry if I have confused you and not
explained completely. My first example is from a form where user enter date
in 1stNotice field and 1stNoticedue fields gets filled by adding 3 days from
1stNotice date. This is an event in my form for 1stNotice field.

Private Sub 1stNotice_Click()
Me.IstNotice = Date
Me.Noticedue = DateAdd("w",7,[1stNotice])

End Sub

This code does not in the least resemble what you describe.

What you describe is "add 3 days".

Your code adds seven days.

Do you want to add three days? or do you want to add seven days?

My second example is for separate Report where I have similar issue. But it
is not in an event it is in the control source for textboxes.

The expression will be the same - just prefix it with an = sign for a control
source.

In this
report, I have one text box that enter Now(). In my second text box I add 3
days to the Now().

Now() does not return today's date. it returns today's date AND TIME, accurate
to a few microseconds. If you don't need the time portion, use Date() instead
of Now().

However, I need workdays/business days only. So if it is
Wednesday, Thursday or Friday, I need to somehow skip Saturday and Sunday.

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

AGAIN: I explained how to do this. You evidently didn't understand it or
didn't try it. Let's try again.

Daysent = Date ' to get today's date rather than #3/16/2008 17:45:21#
DayDue = DateAdd("d", IIF(Weekday(Daysent) > 3, 5, 3), DaySent)

This will check the Weekday of the value in Daysent; if it is 4, 5, 6, 7 -
Wednesday, Thursday, Friday or Saturday - it will add 5 days; if it is 3 or
less (Sunday, Monday, Tuesday) it will add 3 days.

Hope this makes better sense. I hope that you will still be able to help.
Thank you very much for your time and patience.

 

[Arvin Meyer [MVP]]

Yes, tblHolidays is a table of holidays, I typically have 2 fields:
HolidayDate which is a Date/Time field, and is the one used in the code, and
HolidayName which is a text field. I put in any date, other than a weekend
which is handled automatically in the code, that is a holiday, or in the
case I originally wrote this for, a bank holiday.

You would use the code by setting a textbox to:

=GetBusinessDay(#3/25/08#, 3)

or from today's date:

=GetBusinessDay(Date(),3)

To subtract 3 days, use:

=GetBusinessDay(Date(),-3)

Simple as that. It will add or subtract the number of business days from the
date you specify.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thank you for your response. It seems your function looks at a Holiday
table. What would this table consist of? Just Dates? I am sorry for the
questions but I new to code and am trying to understand and apply. Sorry
if
I sound anal but I am trying to incorporate something simple to apply to
what
I already have. I don't have much time in fixing this. I have two
instances
where I need to add business days. The first is a form. The second is a
report. How do I incorporate your function. I know to save it to Module,
how do I call it from either of my samples below. Thank you in advance
for
your help.

The following is in my subform field 1stNotice, Event Click:
Private Sub 1stNotice_Click()

Me.1stNotice= Date
Me.Noticedue = DateAdd("w", 7, [1stNotice])
End Sub

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

The following has been working for me for about 10 years:

http://www.datastrat.com/Code/GetBusinessDay.txt
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Can someone please help how to revise the following by adding only 7
"Business Days".

The following is in my form field date on Click Event:

Private Sub 1stNotice_Click()
Me.1stNotice= Date
Me.Noticedue = DateAdd("w", 7, [1stNotice])
End Sub

The following is in my report field date controls:
Daysent =Now
Daydue=DateAdd("w",3,Now())

Thank you in advance for any help you can provide.