Working day difference between two dates 4

[Lynne41]

Hi

I am trying to calculate the working days difference between 2 dates. I have set up a calendar form to do this which will enter the dates and date difference in a table by inputting into unbound combo boxes which bring the calendar up when clicked. Whatever dates and date difference is in there goes to a table of records.

The names of the combo date fields are cmboStart1 and cmboEnd1 and the days field will do the calculation.

How can I do this ?? Any advice would be appreciated.

Thanks
Lynne

 

[SgtJarrow]

See MichaelRed's faq181-261....everything you need to know.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Lynne41]

Thanks mstrmage1768. How would I apply to my fields on the form though? Where would the code go on my form and how would I declare the variables as my Start, End and Number of days fields?

Thanks
Lynne

 

[SgtJarrow]

First,

Create the table as suggested in the FAQ. Include in the table ANY weekday that is a non-working day for your calculations.

Next take the code from "Public Function..." to "End Function" and paste into in a new module on the module tab. Name the module anything you want EXCEPT DeltaDays.

In your form in the defaultvalue for the number fo days field, put:

=DeltaDays([Start],[End])

That should do the trick.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Lynne41]

Hi

Thanks for this - still doesn't work though. The fields I have on my form are:

cmbStart1 (unbound cmbo box that brings up Active X Calendar) - this is my start date

cmbEnd1 (as above) - this is my end date

txtDays - this is where I want to put the difference in working days between cmbStart1 and cmbEnd1.

How do I incorporate the DeltaDay module into my form? How also can I resolve the issue that the dbs User-defined type is not defined?

Sorry for probably basic questions!

Thanks, Lynne

 

[Lynne41]

Its ok I've sorted it by calling the function in the Control Source of the txtDays field.

Thanks for your help on this mstrmage1768.

Lynne

 

[Rolliee]

Lynn,
I saw your post and just wrote this FAQ. If you will put it in a module, I believe you will find it works well.

start code
==========
Public HDi(12) As Date, iHDi As Integer

Public Function GetDay(ByVal d1 As Date, ByVal d2 As Date) As Long
Dim rs As DAO.Recordset, Numof As Long, i As Integer
Numof = 0

Do While d2 > d1
If good1(d2) Then Numof = Numof + 1
d2 = d2 - 1
Loop
GetDay = Numof
If iHDi = 0 Then
On Error GoTo tellme:
Set rs = CurrentDb.OpenRecordset("holidays&quot
rs.MoveLast: rs.MoveFirst
Do While Not rs.EOF
HDi(i) = rs.Fields(0)
iHDi = iHDi + 1
rs.MoveNext
Loop
GoTo around:
tellme:
If Err.Number = 3078 Then
HDi(0) = CDate("12/25/2004&quot
HDi(1) = CDate("7/4/2004&quot
HDi(2) = CDate("1/1/2005&quot
iHDi = 3
Exit Function
End If
around:
rs.Close
Set rs = Nothing
End If


End Function

Public Function good1(d2 As Date) As Boolean
Dim okay As Boolean, i As Integer

okay = False

Select Case Weekday(d2)
Case 2, 3, 4, 5, 6
okay = True

Case 1, 7
okay = False

End Select
good1 = okay
If okay Then
For i = 0 To iHDi - 1
If HDi(i) = d2 Then okay = False
Next
End If

End Function

=========
end code

You might want to take my birthday from the set holidays. That was a joke.

rollie@bwsys.net

 

[Rolliee]

Also:

'holidays' is a table with the first field a date which is to be considered a holiday. There can be as many as you wish. I may no thave made that clear enough.

Rollie

 

[Lynne41]

Hi Rolliee

I have tried to work this function and have added the relevant UK dates. However this works on a form when called in the Control Source on the calculating no of days field, but am not sure if this is the right place to call?

Also It works when there is one bank holiday but where there are two (ie Easter) it only picks up one of them - in otherwords where someone is off April 9 to April 12 both are bank holidays so no of days should be 0 except its saying 1.

 

[Rolliee]

It can be called from anyform or in any expression. If both your bank holidays are in the list of holidays, neither will be counted as a work day. Any Monday thru Friday whose date is in the holiday table will not be counted. If they occur on a Sat or Sun, they would not be counted anyway. Look at the code and see why each line does what it does. It will really better prepare you for the next job.

rollie

 

[Lynne41]

Thanks for your help Rollie but it is still only counting one day less on the working days (ie 8 April 2004 to 9 April 2004 counts as 1 day when it should be 2). Also it is still not including bank holidays that are in the table. I've even tried american dates in case it was that but where there are 2 bank hol dates together it still says 1 when it should be 0. I guess I'll have to abandon this as there doesn't seem to be a solution. Thanks again.

 

[MichaelRed]

So, you can't implement the faq cited by mstrmage1768 on 2/11/04 above? Even after his further post re the implementation details? What was the (unasked) question which made this not useable for you? Since you said (on 2/16/04) that you were able to use it.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Lynne41]

Hi Michael

I tried to use your code but I have Access2000 which didn't recognise the dim dbs as Database or the FindFirst or Nomatch method. As you can gather I have a very basic understanding of VBA and don't know how to get round this in Access. I changed tactics and changed over to a direct input form based on a table but it still doesn't work. Thanks for your reply though and I'm sure its coz I'm not using your code properly!

 

[SgtJarrow]

Lynne,

You need to set a reference to the DAo object set....this is done by opening any module (code window) click Tools -> References. Find the reference for Microsoft DAO 3.6 Objects and check it. Then, in the code supplied int MichaelRed's FAQ, add "DAO." (without the quotes) in front of Database and Recordset as shown below:

Dim dbs as DAO.Database
Dim rs as DAO.Recordset

This should solve all your problems.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Lynne41]

Thank you mstrmage1768 - you are fantastic! It works perfectly with the exception of 3 Bank Holidays which it is ignoring for some bizarre reason. These are uk dates of course but it is ignoring: 9 Apr 04 ("9/4/04&quot, (12 Apr 04 ("12/4/04&quot and 3 May 04 ("3/5/04&quot which are in the holiday table. All the others are fine. If you can give me a logical explanation for this that would complete it for me!

 

[SgtJarrow]

Are you sure they are being ignored??? Or are the dates being read in US format.....so 9/4/04 would be 4 Sep 04 and 3/5/04 would be 5 Mar 04. Test it by trying some date range that would include one of these dates and see what happens....I would venture to guess this is the issue.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[RoyVidar]

By your examples, and saying you're from the UK, leads me to believe it's a date formatting issue. When not using the US date format, there will be anomalities when doing interaction thru Jet, which expects US date or for instance Ansi formatted date.

Everywhere else, it seems Access works happily with different European date formats (including mine 12.04.2004 is our way of printing 9 apr 04)

I did a very quick test with the below alteration of the criteria line in MichaelRed's faq, and that provided correct results form me (only showint the formatting part).

[tt]... NumSgn & Format$(MyDate, "yyyy-mm-dd&quot & NumSgn[/tt]

Roy-Vidar

 

[Lynne41]

Thanks RoyVidar - that's solved it! Thanks to everyone for helping me with this. You've all been tremendous. I'll leave you in peace now!

 

[sxschech]

Hello,
I tested the getday function last week and it worked great without a problem, today when I try to use it, I get a Run-time error '9': Subscript out of range every other time it is executed. The error happens in the good1 function see bold section. For example, in the immediate window:

Type Result
? getday(now(),#12/5/2004#) 62
? getday(now(),#12/5/2004#) Error {Click End}
? getday(now(),#12/5/2004#) 62
? getday(now(),#12/5/2004#) Error {Click End}

This happens regardless of what date I enter.
I am using access 2000 and have set a reference to DAO 3.6 and have also moved it above ADO.

When I comment out the line of code that is causing the error, the code runs, if I leave it commented out, would I be missing a calculation as I seem to still get the correct result whether the line is commented or not on the above example date and a few other dates that I tried.

Public Function good1(d2 As Date) As Boolean
Dim okay As Boolean, i As Integer

okay = False

Select Case Weekday(d2)
  Case 2, 3, 4, 5, 6
    okay = True
 
  Case 1, 7
    okay = False

End Select
good1 = okay
If okay Then
     For i = 0 To iHDi - 1
       [b]If HDi(i) = d2 Then okay = False[/b]
    Next
End If

End Function

Thank you for your consideration of this matter.

 

[RoyVidar]

Try MichaelRed's DeltaDays (linked by mstrmage1768 in their first post).

Roy-Vidar