Date plus 10 Working days

[Stockwell43]

Hello,

Is there a way to place a text box on a form that will retrieve a Date from
the following:

OriginalDate +10 Working days (week days no weekends)
I want the +10 Working days date in the textbox. We'll call it "CutoffDate"

Thanks!!

 

[Jeff Boyce]

While there are routines (try checking mvps.org/access) to "calculate"
"working days", won't that really depend on whether you have a table of
holidays. I assume you wish to eliminate not only weekends but holidays,
too.

After all, if the only thing you wanted to do was add 10 working days, you
could just add 2 weeks (since weekends add two more days each).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

By the way, take a look at the DateAdd() function. You can use it to add
units-of-time (e.g., minutes, hours, days, weeks, etc) to a date. You can
add an event procedure to your [OriginalDate] field that calculates the new
date value and places that in your textbox.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Arvin Meyer [MVP]]

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

is exactly what you want.

 

[Dirk Goldgar]

Hello,

Is there a way to place a text box on a form that will retrieve a Date
from
the following:

OriginalDate +10 Working days (week days no weekends)
I want the +10 Working days date in the textbox. We'll call it
"CutoffDate"

(My first reply to this post doesn't seem to have made it to the newsgroup.)

See this link:

http://www.mvps.org/access/datetime/date0012.htm
Date/Time: Doing WorkDay Math in VBA

With the functions posted there saved in a standard module, you could set
the controlsource of CutoffDate to

=dhAddWorkDaysA(10, [OriginalDate])

Note that, as a calculated value, the value of CutoffDate would not normally
be saved in your form's underlying table.

 

[Stockwell43]

Hi Guys, thank you for all your responses!!!

Jeff, you correct about the holidays. My initial thought was to use
turnaroun time in number of workdays instead of the date and place a checkbox
on the form that would subtract one from the turntime if a holiday fell
within that range. I decided to show the date instead to give the manager a
visual.

I did try the DateAdd but it only adds 10 days not 10 working days.

Dirk, I tried your recommendation and it worked fine but it was adding 10
dates to the Original date so if I have 7/15/2008 in original date,
CutoffDate displayed 7/25/2008 unless I did something wrong which I wouldn't
be surprised.

Also, you correct, it will not be placed in the table. it's more for a report.

Arvin, I tried yours and I get #Nam! in the CutoffDate box. I have this in
the control Source =GetBusinessDay(10,[OriginalDate]) Should I have it in an
event procedure instead?

Guys,

I am sure I am doing something wrong. I pasted the enitre code from each in
separate modules (Arvin Module 3, Dirk Module 5) and I can't seem to get it
right. Sorry about this. Any suggestions?

Thanks!

 

[Stockwell43]

Hi Dirk,

I added all the code from the site and placed it in a module. I place the
line you gave me in my control source and when I open the form if it's a new
record, I get Error# in the CutoffDate field and if there is an OriginalDate
then it gives me a date in the CutoffDate field but it is 10 days from the
Original Date not 10 working days. I'm sure I must have done something wrong.

Hello,

Is there a way to place a text box on a form that will retrieve a Date
from
the following:

OriginalDate +10 Working days (week days no weekends)
I want the +10 Working days date in the textbox. We'll call it
"CutoffDate"

(My first reply to this post doesn't seem to have made it to the newsgroup.)

See this link:

http://www.mvps.org/access/datetime/date0012.htm
Date/Time: Doing WorkDay Math in VBA

With the functions posted there saved in a standard module, you could set
the controlsource of CutoffDate to

=dhAddWorkDaysA(10, [OriginalDate])

Note that, as a calculated value, the value of CutoffDate would not normally
be saved in your form's underlying table.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Krzysztof Pozorek [MVP]]

(...)

Is there a way to place a text box on a form that will retrieve a Date
from
the following:

OriginalDate +10 Working days (week days no weekends)
I want the +10 Working days date in the textbox. We'll call it
"CutoffDate"

Write the following function:

Public Function GetD(OrigD As Date, n As Long)
Dim i As Long
Do While i < n
OrigD = OrigD + 1
If Weekday(OrigD, 2) < 6 Then i = i + 1
Loop
GetD = OrigD
End Function

How to use:

YourDate = GetD(Date, 6)

K.P.

 

[Arvin Meyer [MVP]]

Arvin, I tried yours and I get #Nam! in the CutoffDate box. I have this in
the control Source =GetBusinessDay(10,[OriginalDate]) Should I have it in
an
event procedure instead?

I usually use an event procedure, but you have it backwards anyway, and this
might fix it:


= GetBusinessDay([Forms]![Your Correct Form Name]![OriginalDate],10)

 

[Stockwell43]

Hi,

Thank you for your reply.

I place your code in a module name Module12

The other piece under How to Use I place in the Control Source of the
textbox I want to display the CutoffDate. I changed the YourDate to Original
date as this is the date I want to be the driver. and I a get #Name? Did I do
something wrong?

 

[Dirk Goldgar]

I added all the code from the site and placed it in a module. I place the
line you gave me in my control source and when I open the form if it's a
new
record, I get Error# in the CutoffDate field and if there is an
OriginalDate
then it gives me a date in the CutoffDate field but it is 10 days from the
Original Date not 10 working days. I'm sure I must have done something
wrong.

As far as the workday addition data not being right, I can't understand
that, as it absolutely gives me the correct answer here. Are you sure you
didn't use a DateAdd expression in the controlsource instead of
dhAddWorkdaysA?

Yes, it will give you #Error if there is no date in OriginalDate. If you
don't want to see that, surround the function call with an IIf() expression
to test for that, like this:

=IIf(IsDate([OriginalDate]),dhAddWorkdaysA(10,[OriginalDate]),Null)

 

[Stockwell43]

Arvin,

It works fine except for one thing:

If the Original Date field is blank, in the CutoffDate field it show #Error?
But if I put the date in the Original Date field it works perfectly.

Please explain me having it backwards. Should I not have placed the code in
a module? It's obvious I messed up what needed to go into the Control Source.
Is that what you were referring too? I need to know specifically so I can
update my notes for the next time I have to do this.

Thank you very much for your help with this, most appreciated!!

Arvin, I tried yours and I get #Nam! in the CutoffDate box. I have this in
the control Source =GetBusinessDay(10,[OriginalDate]) Should I have it in
an
event procedure instead?

I usually use an event procedure, but you have it backwards anyway, and this
might fix it:


= GetBusinessDay([Forms]![Your Correct Form Name]![OriginalDate],10)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Stockwell43]

Hi Dirk,

It does work with both lines you gave me but I like the one that doesn't
show the error if Original Date is null. Do you suppose I needed to close out
of the database first? I copy and pasted it in the Control source and swear
it only counted 10 days not working days.

Oh well, the important thing is that it's working now.

I want to thank you for your help. All of you who reply really helped me out
which is most appreciated. I made notes and will now have it for the next
time I come across this.

I added all the code from the site and placed it in a module. I place the
line you gave me in my control source and when I open the form if it's a
new
record, I get Error# in the CutoffDate field and if there is an
OriginalDate
then it gives me a date in the CutoffDate field but it is 10 days from the
Original Date not 10 working days. I'm sure I must have done something
wrong.

As far as the workday addition data not being right, I can't understand
that, as it absolutely gives me the correct answer here. Are you sure you
didn't use a DateAdd expression in the controlsource instead of
dhAddWorkdaysA?

Yes, it will give you #Error if there is no date in OriginalDate. If you
don't want to see that, surround the function call with an IIf() expression
to test for that, like this:

=IIf(IsDate([OriginalDate]),dhAddWorkdaysA(10,[OriginalDate]),Null)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Stockwell43]

Dirk, I have just one other question:

I type in 7/3 and 7/17 is returned not taking out 7/4. I see something about
SkipHolidays. Am I suppose to have a table of some sort with the holidays
listed?

Thanks!

I added all the code from the site and placed it in a module. I place the
line you gave me in my control source and when I open the form if it's a
new
record, I get Error# in the CutoffDate field and if there is an
OriginalDate
then it gives me a date in the CutoffDate field but it is 10 days from the
Original Date not 10 working days. I'm sure I must have done something
wrong.

As far as the workday addition data not being right, I can't understand
that, as it absolutely gives me the correct answer here. Are you sure you
didn't use a DateAdd expression in the controlsource instead of
dhAddWorkdaysA?

Yes, it will give you #Error if there is no date in OriginalDate. If you
don't want to see that, surround the function call with an IIf() expression
to test for that, like this:

=IIf(IsDate([OriginalDate]),dhAddWorkdaysA(10,[OriginalDate]),Null)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Stockwell43]

Got it Arvin. Thank you!!!

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

is exactly what you want.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[John W. Vinson/MVP]

I type in 7/3 and 7/17 is returned not taking out 7/4. I see something about
SkipHolidays. Am I suppose to have a table of some sort with the holidays
listed?

Ummmm...

Yes.

How on Earth would Access know which holidays *your* company observes,
unless you tell it?

There is a 4th of July in Australia. They don't typically observe it
as a holiday, though.

 

[Dirk Goldgar]

Dirk, I have just one other question:

I type in 7/3 and 7/17 is returned not taking out 7/4. I see something
about
SkipHolidays. Am I suppose to have a table of some sort with the holidays
listed?

Various functions for handling workday math provide different methods for
dealing with holidays. Many of them use a table of holidays. The function
dhAddWorkDaysA that I pointed you to accepts and optional array of holiday
dates, and gives an example in the comments of calling it and passing some
hard-coded holidays. Basically, the function leaves it up to you to
generate and provide an array of holidays.

One way to handle this is to have a list of holidays stored in a table, and
create function that loads that table into an array and returns the array.
For example:

'----- start of code -----
Function HolidayTableToArray(Optional fReset As Boolean) As Variant

' Written by: Dirk Goldgar, DataGnostics LLC
' You may use this code in your applications, but please leave
' the attribution unchanged.

Dim rs As DAO.Recordset
Static adtHolidays() As Date
Static fLoaded As Boolean

If fReset Then
fLoaded = False
End If

If Not fLoaded Then
Erase adtHolidays
Set rs = CurrentDb.OpenRecordset("SELECT HolidayDate FROM
tblHolidays", dbOpenSnapshot)
With rs
If Not .EOF Then
.MoveLast
.MoveFirst
End If
If .RecordCount > 0 Then
ReDim adtHolidays(.RecordCount - 1)
Do Until .EOF
adtHolidays(.AbsolutePosition) = !HolidayDate
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
fLoaded = True
End If

HolidayTableToArray = adtHolidays

End Function
'----- end of code -----

The above code assumes you have a table named "tblHolidays", containing a
date field named "HolidayDate", and each record in that table specifies the
date of a holiday to be observed. Given such a table and the function
above, you can reference the function in your call to dhAddWorkDaysA:

=dhAddWorkDaysA(10, [OriginalDate], HolidayTableToArray())

 

[Stockwell43]

Hi Dirk,

Ok, the picture is much more clear now. I did set up the table and field as
shown and all is well. Thank you for staying with me on this and I made my
notes for next time (which I'm sure there will be).

Although I am not a programmer by trade, I wish I could have half the
knowledge about Access as you guys do. My work life would be so much easier
when I have to do something in Access. )

Thanks Again!!!!

Dirk, I have just one other question:

I type in 7/3 and 7/17 is returned not taking out 7/4. I see something
about
SkipHolidays. Am I suppose to have a table of some sort with the holidays
listed?

Various functions for handling workday math provide different methods for
dealing with holidays. Many of them use a table of holidays. The function
dhAddWorkDaysA that I pointed you to accepts and optional array of holiday
dates, and gives an example in the comments of calling it and passing some
hard-coded holidays. Basically, the function leaves it up to you to
generate and provide an array of holidays.

One way to handle this is to have a list of holidays stored in a table, and
create function that loads that table into an array and returns the array.
For example:

'----- start of code -----
Function HolidayTableToArray(Optional fReset As Boolean) As Variant

' Written by: Dirk Goldgar, DataGnostics LLC
' You may use this code in your applications, but please leave
' the attribution unchanged.

Dim rs As DAO.Recordset
Static adtHolidays() As Date
Static fLoaded As Boolean

If fReset Then
fLoaded = False
End If

If Not fLoaded Then
Erase adtHolidays
Set rs = CurrentDb.OpenRecordset("SELECT HolidayDate FROM
tblHolidays", dbOpenSnapshot)
With rs
If Not .EOF Then
.MoveLast
.MoveFirst
End If
If .RecordCount > 0 Then
ReDim adtHolidays(.RecordCount - 1)
Do Until .EOF
adtHolidays(.AbsolutePosition) = !HolidayDate
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
fLoaded = True
End If

HolidayTableToArray = adtHolidays

End Function
'----- end of code -----

The above code assumes you have a table named "tblHolidays", containing a
date field named "HolidayDate", and each record in that table specifies the
date of a holiday to be observed. Given such a table and the function
above, you can reference the function in your call to dhAddWorkDaysA:

=dhAddWorkDaysA(10, [OriginalDate], HolidayTableToArray())


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Stockwell43]

Hi John,

I know, I wrote it before thinking and already click the send button. I
guess in the back of my mind I was hoping for a miracle. )

Thanks!!!

 

[Arvin Meyer [MVP]]

The date is not optional, nor could it be, since you need a date to start
with. You could use the AfterUpdate event of the OriginalDate control to run
the code.

Yes, it should be a standard module. You had the 2 arguments backwards (the
start date and the number of days to add or subtract)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,

It works fine except for one thing:

If the Original Date field is blank, in the CutoffDate field it show
#Error?
But if I put the date in the Original Date field it works perfectly.

Please explain me having it backwards. Should I not have placed the code
in
a module? It's obvious I messed up what needed to go into the Control
Source.
Is that what you were referring too? I need to know specifically so I can
update my notes for the next time I have to do this.

Thank you very much for your help with this, most appreciated!!

Arvin, I tried yours and I get #Nam! in the CutoffDate box. I have this
in
the control Source =GetBusinessDay(10,[OriginalDate]) Should I have it
in
an
event procedure instead?

I usually use an event procedure, but you have it backwards anyway, and
this
might fix it:


= GetBusinessDay([Forms]![Your Correct Form Name]![OriginalDate],10)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com