Working Days Function in Access

  • Thread starter Thread starter Maldo
  • Start date Start date
M

Maldo

I have a user form that has a datein and dueby text box. The datein field
populates with the current date while the dueby field is 5 business days from
datein excluding identified holidays. All this happens once the user saves a
record. I was able to get this to work using the code found at
http://www.mvps.org/access/datetime/date0012.htm. I would like to take this
a step further. I want to add a business day if the user saves a record
after a specified time (say 10:00 AM). How can I modify the code to get this
to work? Any advice is really appreciated. Thanx!
 
The link you provided doesn't work for me, but if you want to do what you are
asking, all you need is to check the time and add a day if it is after 10 AM:

If Hour(SomeDate) >= 10 then
SomeDate = DateAdd("d",1,SomeDate)
End If

Also, be aware that this should be done prior to calculating the dates.
 
Sorry about the link. Try this one
http://www.mvps.org/access/datetime/date0012.htm

I was not aware of these two functions. It appears that your simple code
will work. I will test and let you know. If the link above worked and you
find that your recommended code may not work for this purpose, please let me
know.

Thanks for you repsonse!!
 
I am not that crazy about the code on the web site. Having to add a list of
holiday dates hardcoded is not the greatest idea. That means every year you
have to modify the code and redeploy your database.
I would suggest creating a holiday table with all the holidays in it.
If you have an interest, I have a function I use for this purpose.
 
The hour and dateadd functions you recommeded worked like a charm. Thanks
for your help!! I had the same concern about the code and the hardcoded
holiday array. I planned on creating a table to hold the holiday dates and
read them into into the array. Is this how your function works?
 
No, it uses a DCount to count the number of holidays in the table between the
two dates.

First see if the date needs to be bumped by one.
Then calculate an ending date using the weekdays.
Then count the holidays between the dates.
Then add a day for each holiday found and check for weekends in the new dates.
 
I think I follow your methodology. I will give it a try. Perhaps you can
you post a sample of your function if you have the opportunity so that I may
compare if I can't get my code to work. Thnx!
 
I was out of the office yesterday and wanted to continue with this project
today. Any chance I could take a peak at the function you created? Thanks
 
Sorry, I got busy and did not get it done yesterday.
The function works a little bit different than I told you. It has been a
long time since I looked at the code and I had it confused with a different
function that returns the number of days between two dates. It actually
loops through the holiday file to determine the number of days. Anyway, here
it is:

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = dtmReturnDate
End Function
 
Wow! Your code is straightforward, cleaner and much easier to understand
than the function(s) I was trying to use. I tested it in my database and it
works beautifully. It serves the same purpose but much more efficiently. I
really appreaciate you offering your expertise to assist me with this
project. You have saved me so much time. I aspire to be as good as you and
your fellow MVPs. Thanks a bunch!!! Have a great day...

Klatuu said:
Sorry, I got busy and did not get it done yesterday.
The function works a little bit different than I told you. It has been a
long time since I looked at the code and I had it confused with a different
function that returns the number of days between two dates. It actually
loops through the holiday file to determine the number of days. Anyway, here
it is:

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = dtmReturnDate
End Function

--
Dave Hargis, Microsoft Access MVP


Maldo said:
I was out of the office yesterday and wanted to continue with this project
today. Any chance I could take a peak at the function you created? Thanks
Click to expand...
Click to expand...
 
Glad it worked for you.
I appreciate your kind comments about my code. It describes exactly what I
aspire to.
--
Dave Hargis, Microsoft Access MVP


Maldo said:
Wow! Your code is straightforward, cleaner and much easier to understand
than the function(s) I was trying to use. I tested it in my database and it
works beautifully. It serves the same purpose but much more efficiently. I
really appreaciate you offering your expertise to assist me with this
project. You have saved me so much time. I aspire to be as good as you and
your fellow MVPs. Thanks a bunch!!! Have a great day...

Klatuu said:
Sorry, I got busy and did not get it done yesterday.
The function works a little bit different than I told you. It has been a
long time since I looked at the code and I had it confused with a different
function that returns the number of days between two dates. It actually
loops through the holiday file to determine the number of days. Anyway, here
it is:

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = dtmReturnDate
End Function

--
Dave Hargis, Microsoft Access MVP


Maldo said:
I was out of the office yesterday and wanted to continue with this project
today. Any chance I could take a peak at the function you created? Thanks

:

I am about ready to walk out the door. I will get it to you tomorrow.
--
Dave Hargis, Microsoft Access MVP


:

I think I follow your methodology. I will give it a try. Perhaps you can
you post a sample of your function if you have the opportunity so that I may
compare if I can't get my code to work. Thnx!

:

No, it uses a DCount to count the number of holidays in the table between the
two dates.

First see if the date needs to be bumped by one.
Then calculate an ending date using the weekdays.
Then count the holidays between the dates.
Then add a day for each holiday found and check for weekends in the new dates.
--
Dave Hargis, Microsoft Access MVP


:

The hour and dateadd functions you recommeded worked like a charm. Thanks
for your help!! I had the same concern about the code and the hardcoded
holiday array. I planned on creating a table to hold the holiday dates and
read them into into the array. Is this how your function works?


:

I am not that crazy about the code on the web site. Having to add a list of
holiday dates hardcoded is not the greatest idea. That means every year you
have to modify the code and redeploy your database.
I would suggest creating a holiday table with all the holidays in it.
If you have an interest, I have a function I use for this purpose.
--
Dave Hargis, Microsoft Access MVP


:

Sorry about the link. Try this one
http://www.mvps.org/access/datetime/date0012.htm

I was not aware of these two functions. It appears that your simple code
will work. I will test and let you know. If the link above worked and you
find that your recommended code may not work for this purpose, please let me
know.

Thanks for you repsonse!!


:

The link you provided doesn't work for me, but if you want to do what you are
asking, all you need is to check the time and add a day if it is after 10 AM:

If Hour(SomeDate) >= 10 then
SomeDate = DateAdd("d",1,SomeDate)
End If

Also, be aware that this should be done prior to calculating the dates.
--
Dave Hargis, Microsoft Access MVP


:

I have a user form that has a datein and dueby text box. The datein field
populates with the current date while the dueby field is 5 business days from
datein excluding identified holidays. All this happens once the user saves a
record. I was able to get this to work using the code found at
http://www.mvps.org/access/datetime/date0012.htm. I would like to take this
a step further. I want to add a business day if the user saves a record
after a specified time (say 10:00 AM). How can I modify the code to get this
to work? Any advice is really appreciated. Thanx!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top