Adding Business Days only

  • Thread starter Thread starter TotallyConfused
  • Start date Start date
T

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.
 
Is there may some sql that would work?
Click to expand...

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.
 
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())
 
TotallyConfused said:
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())



John W. Vinson said:
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.
Click to expand...
Click to expand...

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


Harold Druss said:
TotallyConfused said:
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())



John W. Vinson said:
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.
Click to expand...
Click to expand...

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
Click to expand...
 
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
Click to expand...

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.
 
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))



John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
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.
Click to expand...
Click to expand...
 
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())
Click to expand...
Click to expand...





Arvin Meyer said:
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

TotallyConfused said:
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.
Click to expand...
Click to expand...
 
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())



raskew via AccessMonster.com said:
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
Click to expand...
[quoted text clipped - 27 lines]
Tweak to fit your actual business rule which I don't quite understand given
your examples.
Click to expand...
Click to expand...
Click to expand...
 
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))
Click to expand...

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])
Click to expand...
Click to expand...
 
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.



John W. Vinson said:
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))
Click to expand...

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])
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...

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.
Click to expand...

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().
Click to expand...

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())
Click to expand...

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.
 
7 Seven days for 1st Notice in my form. 3 days for my report. Thank you.

John W. Vinson said:
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
Click to expand...

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.
Click to expand...

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().
Click to expand...

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())
Click to expand...

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.
Click to expand...
Click to expand...
 
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.

John W. Vinson said:
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
Click to expand...

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.
Click to expand...

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().
Click to expand...

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())
Click to expand...

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.
Click to expand...
Click to expand...
 
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

TotallyConfused said:
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())
Click to expand...
Click to expand...





Arvin Meyer said:
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

TotallyConfused said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top