Date plus 10 Working days

S

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!!
 
J

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
 
J

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
 
D

Dirk Goldgar

Stockwell43 said:
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.
 
S

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!
 
S

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.

Dirk Goldgar said:
Stockwell43 said:
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)
 
K

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

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

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?
 
D

Dirk Goldgar

Stockwell43 said:
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)
 
S

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

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.



Dirk Goldgar said:
Stockwell43 said:
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)
 
S

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!

Dirk Goldgar said:
Stockwell43 said:
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)
 
J

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

Dirk Goldgar

Stockwell43 said:
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())
 
S

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

Thanks Again!!!!

Dirk Goldgar said:
Stockwell43 said:
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)
 
S

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

Thanks!!!
 
A

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


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top