Solved

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

Posted on 2011-05-06
519 Views
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

LVL 12

Expert Comment

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

HTH,
Daniel
0

LVL 119

Expert Comment

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

LVL 48

Accepted Solution

0

Author Comment

@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

LVL 119

Expert Comment

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

getWorkDays(DATEFROM, DATETO)
0

LVL 119

Expert Comment

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

Author Comment

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

LVL 119

Expert Comment

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

LVL 48

Expert Comment

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

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

/gustav
0

Author Comment

@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

LVL 119

Expert Comment

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

* and Please  do not change the codes
0

Author Comment

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 Comment

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 Comment

@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

LVL 119

Expert Comment

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

LVL 48

Expert Comment

> * 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 Comment

@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

LVL 119

Expert Comment

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

LVL 48

Expert Comment

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

LVL 119

Expert Comment

then use this

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

LVL 48

Expert Comment

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

@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 Comment

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

LVL 119

Expert Comment

did you try my post at http#a35707650
0

LVL 48

Expert Comment

> 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

LVL 119

Expert Comment

did you try my post at http:#a35707650
0

LVL 48

Expert Comment

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

@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 Comment

@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

LVL 119

Expert Comment

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

Author Comment

@capricorn1

The same thing happened.
0

LVL 48

Expert Comment

You are welcome!

/gustav
0

Featured Post

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6Ã—7 is, you just type â€œ=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the reâ€¦
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question IDÂ  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (httpâ€¦
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process fromâ€¦