• Status: Solved
• Priority: Medium
• Security: Public
• Views: 538

# Calculating days between two dates in access form that excludes weekends and holidays?

Hi,

After producing a holiday submission request form with access I am trying to implement an additional function that calculates the amount of dates between the two dates, however, I want it to minus off weekends and if possible holidays (bank holidays etc) as well.

Would I need to have a module running in order to do such a calculation?

Thanks.
0
• 12
• 11
• 8
• +1
1 Solution

Commented:
Check this link out on how to accomplish this;
http://access.mvps.org/access/datetime/date0012.htm

HTH,
Daniel
0

Commented:
try this codes

``````Function getWorkDays(vDate1 As Date, vDate2 As Date) As Long
Dim i As Long, dtStart
If vDate1 = vDate2 Then getWorkDays = 0: Exit Function
If vDate1 < vDate2 Then
dtStart = vDate1
i = DateDiff("d", vDate1, vDate2) '+ 1
Do Until dtStart >= vDate2
dtStart = dtStart + 1
Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
Or Not IsNull(DLookup("[Date]", "tbl_Holidays", "[Date]=#" _
& dtStart & "#"))
dtStart = dtStart + 1
i = i - 1
Loop
Loop
Else
dtStart = vDate2
i = DateDiff("d", vDate2, vDate1) '+ 1
Do Until dtStart >= vDate1
dtStart = dtStart + 1
Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
Or Not IsNull(DLookup("[Date]", "tbl_Holidays", "[Date]=#" _
& dtStart & "#"))
dtStart = dtStart + 1
i = i - 1
Loop
Loop
End If

getWorkDays = i

End Function
``````
0

CIOCommented:
0

Author Commented:
@capricorn1

Thanks, there are quite a few variables in the code, just to make things easier I have a table called tblHolidays with two fields, one named holidate(date) and the other holiname(text).  I presume that a necessary change would be to amend where it says tbl_holidays to tblholidays but what about the the sections where it says [date] and at the beginning of the code it states vDate1 and vDate2?

The form name is f_malings and there are three boxes, DATEFROM, DATETO and COUNTDAYS.
0

Commented:
vDate1 and vDate2 are the starting and ending dates, so it will be

getWorkDays(DATEFROM, DATETO)
0

Commented:

me.COUNTDAYS= getWorkDays(me.DATEFROM, me.DATETO)
0

Author Commented:
Made the changes, thanks, it shows a figure but it appears to be wrong.

Checking the Calendar, taking time off from the 1st Dec to 8th Dec this year should be 6 days but in the box it shows as 7?

0

Commented:
try this revised codes

``````Function getWorkDays(vDate1 As Date, vDate2 As Date) As Long
Dim i As Long, dtStart
If vDate1 = vDate2 Then getWorkDays = 0: Exit Function
If vDate1 < vDate2 Then
dtStart = vDate1
i = DateDiff("d", vDate1, vDate2) + 1
Do Until dtStart >= vDate2
dtStart = dtStart + 1
Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
Or Not IsNull(DLookup("[Date]", "tbl_Holidays", "[Date]=#" _
& dtStart & "#"))
dtStart = dtStart + 1
i = i - 1
Loop
Loop
Else
dtStart = vDate2
i = DateDiff("d", vDate2, vDate1) + 1
Do Until dtStart >= vDate1
dtStart = dtStart + 1
Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
Or Not IsNull(DLookup("[Date]", "tbl_Holidays", "[Date]=#" _
& dtStart & "#"))
dtStart = dtStart + 1
i = i - 1
Loop
Loop
End If

getWorkDays = i

End Function
``````

0

CIOCommented:
Use the function from my link above. It does it correctly.

=ISO_WorkDayDiff([DATEFROM],[DATETO],True)

/gustav
0

Author Commented:
@capricorn1

Done that and now it shows as 8 days?

The code below is what I have in the module

Function getWorkDays(DATETO As Date, DATEFROM As Date) As Long
Dim i As Long, dtStart
If DATETO = DATEFROM Then getWorkDays = 0: Exit Function
If DATETO < DATEFROM Then
dtStart = DATETO
i = DateDiff("d", DATETO, DATEFROM) '+ 1
Do Until dtStart >= DATEFROM
dtStart = dtStart + 1
Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
Or Not IsNull(DLookup("[Date]", "tblHolidays", "[Date]=#" _
& dtStart & "#"))
dtStart = dtStart + 1
i = i - 1
Loop
Loop
Else
dtStart = DATEFROM
i = DateDiff("d", DATEFROM, DATETO) + 1
Do Until dtStart >= DATEFROM
dtStart = dtStart + 1
Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
Or Not IsNull(DLookup("[DATETO]", "tblHolidays", "[DATEFROM]=#" & dtStart & "#"))
dtStart = dtStart + 1
i = i - 1
Loop
Loop
End If

getWorkDays = i

End Function
-------------------

0

Commented:
your table "tblHolidays" must have this fields Date that will contain the dates of holidays

* and Please  do not change the codes
0

Author Commented:
Sorry I'm being a bit daft, the table tblholidays is not supposed to store any data but in order for the function to work DATETO and DATEFROM need to be fields in the tblholidays table?
0

Author Commented:
The reason I posted the above was because my tblholidays is supposed to just hold records of holidays throughout the year e..g bank holidays etc.
0

Author Commented:
@cactus_data

I just tried the code you showed and received a run time error 2001, msg was you canceled the previous operation.

in vb script debugger it shows the following highlighted in yellow

lngHolidays = DCount("*", cstrTableHoliday, strFilter)
0

Commented:
what is the name of the field in table tblholidays that contain the holidates? post it here
0

CIOCommented:
> * and Please  do not change the codes

Well, you have to do so.

Use the function from my link above. It does it correctly.

> .. the table tblholidays is not supposed to store any data but in order for the
> function to work DATETO and DATEFROM need to be fields in the tblholidays table?

No, just skip the holiday check:

=ISO_WorkDayDiff([DATEFROM],[DATETO],False)

/gustav
0

Author Commented:
@capricorn1

tblholidays has two fields, holidates and holiname, the holidates is a date field containing dates e.g. 25/12/2011 and holiname is just a description field.

@cactus_data

just tried your code and the control source value of
=ISO_WorkDayDiff([DATEFROM],[DATETO],False) and i received run time error as stated in the previous post.
0

Commented:
ok , copy and paste this

``````Function getWorkDays(vDate1 As Date, vDate2 As Date) As Long
Dim i As Long, dtStart
If vDate1 = vDate2 Then getWorkDays = 0: Exit Function
If vDate1 < vDate2 Then
dtStart = vDate1
i = DateDiff("d", vDate1, vDate2) + 1
Do Until dtStart >= vDate2
dtStart = dtStart + 1
Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
Or Not IsNull(DLookup("Holidates", "tblHolidays", "Holidates=#" _
& dtStart & "#"))
dtStart = dtStart + 1
i = i - 1
Loop
Loop
Else
dtStart = vDate2
i = DateDiff("d", vDate2, vDate1) + 1
Do Until dtStart >= vDate1
dtStart = dtStart + 1
Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
Or Not IsNull(DLookup("Holidates", "tblHolidays", "Holidates=#" _
& dtStart & "#"))
dtStart = dtStart + 1
i = i - 1
Loop
Loop
End If

getWorkDays = i

End Function
``````
0

CIOCommented:
When running with the holiday check, modify these lines to fit your holiday table:

' Name of table with holidays.
Const cstrTableHoliday    As String = "tblHoliday"
' Name of date field in holiday table.
Const cstrFieldHoliday    As String = "HolidayDate"

/gustav
0

Commented:
then use this

me.COUNTDAYS= getWorkDays(me.DATEFROM, me.DATETO)
0

CIOCommented:
> .. taking time off from the 1st Dec to 8th Dec this year should be 6 days ..

Acutally, it should be 5 days as you normally don't include both the start and the end date.
If, however, you alway have a workday as the start date, just add 1 to include this in the count.

/gustav

0

Author Commented:
@cactus_data

Strange that it seems I am back to same point as I was with capricorn1's code.

It says that it's 5 days for the dates of 4th August to 11th August when it should be 6.

Would I be correct in saying that the days are not inclusive of the last date?  E.g. 4th to 11th is saying that I'm off on the 4th and back on the 11th?  Is it possible to change the code to include the 11th?
0

Author Commented:
Ah sorry just saw your post, so at the end of control source, just put +1 at the end.

Would this be suited in the module code or within the control source.

The control source shows as:

=ISO_WorkDayDiff([DATEFROM],[DATETO],True)
0

Commented:
did you try my post at http#a35707650
0

CIOCommented:
> just tried your code and the control source value of
> =ISO_WorkDayDiff([DATEFROM],[DATETO],False) and
> i received run time error as stated in the previous post.

That just can't be true. Try again.

/gustav
0

Commented:

did you try my post at http:#a35707650
0

CIOCommented:
> Would this be suited in the module code or within the control source.

> The control source shows as:

=ISO_WorkDayDiff([DATEFROM],[DATETO],True)

Exactly, or if you like:

=ISO_WorkDayDiff([DATEFROM],[DATETO],True)+1

And don't forget to adjust the two constants with you table and field names.

/gustav
0

Author Commented:
@capricorn1

I'm trying that now

@cactus_data

Not to worry, gone past that now and looking to add the 1 day, see previous post.
0

Author Commented:
@cactus_data

Okay, that now works, also noted that the holiday dates have to be readjusted because Christmas, Boxing Day and New Years all fall on Sunday therefore the days have to be put down as Monday and Tuesday, works perfectly, much appreciated.

@capricorn1

I just tried the code you put up in a previous post and received a run time error 2001, msg was you canceled the previous operation.

in vb script debugger it shows the following highlighted in yellow

Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
Or Not IsNull(DLookup("[Date]", "tblHolidays", "[Date]=#" _
& dtStart & "#"))
0

Commented:
i said copy and paste the revised codes from my post at http:#a35707650
0

Author Commented:
@capricorn1

The same thing happened.
0

CIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.