• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 534
  • Last Modified:

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
jm-itadmin
Asked:
jm-itadmin
  • 12
  • 11
  • 8
  • +1
1 Solution
 
danishaniCommented:
Check this link out on how to accomplish this;
http://access.mvps.org/access/datetime/date0012.htm

HTH,
Daniel
0
 
Rey Obrero (Capricorn1)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

Open in new window

0
 
Gustav BrockCIOCommented:
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jm-itadminAuthor 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
 
Rey Obrero (Capricorn1)Commented:
vDate1 and vDate2 are the starting and ending dates, so it will be

   getWorkDays(DATEFROM, DATETO)
0
 
Rey Obrero (Capricorn1)Commented:

me.COUNTDAYS= getWorkDays(me.DATEFROM, me.DATETO)
0
 
jm-itadminAuthor 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
 
Rey Obrero (Capricorn1)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

Open in new window




0
 
Gustav BrockCIOCommented:
Use the function from my link above. It does it correctly.

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

/gustav
0
 
jm-itadminAuthor 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
 
Rey Obrero (Capricorn1)Commented:
your table "tblHolidays" must have this fields Date that will contain the dates of holidays

* and Please  do not change the codes
0
 
jm-itadminAuthor 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
 
jm-itadminAuthor 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
 
jm-itadminAuthor 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
 
Rey Obrero (Capricorn1)Commented:
what is the name of the field in table tblholidays that contain the holidates? post it here
0
 
Gustav BrockCIOCommented:
> * 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
 
jm-itadminAuthor 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
 
Rey Obrero (Capricorn1)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

Open in new window

0
 
Gustav BrockCIOCommented:
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
 
Rey Obrero (Capricorn1)Commented:
then use this

me.COUNTDAYS= getWorkDays(me.DATEFROM, me.DATETO)
0
 
Gustav BrockCIOCommented:
> .. 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
 
jm-itadminAuthor 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
 
jm-itadminAuthor 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
 
Rey Obrero (Capricorn1)Commented:
jm-itadmin
did you try my post at http#a35707650
0
 
Gustav BrockCIOCommented:
> 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
 
Rey Obrero (Capricorn1)Commented:


jm-itadmin
did you try my post at http:#a35707650
0
 
Gustav BrockCIOCommented:
> 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
 
jm-itadminAuthor 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
 
jm-itadminAuthor 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
 
Rey Obrero (Capricorn1)Commented:
jm-itadmin,
i said copy and paste the revised codes from my post at http:#a35707650
0
 
jm-itadminAuthor Commented:
@capricorn1

The same thing happened.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 12
  • 11
  • 8
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now