Date Time difference in Days, Hrs, Mins (1 Viewer)

[martinjward]

I am trying to write a subroutine (which resides in a module) to calculate the number of days, hours and minuites between to date times (I have searched forums and only found working days calculations , no working hours).

I am trying to discount bank hols and weekends and this is what I can up with....

Public Sub DayDifference(dtestartdate As Date, dteenddate As Date)

Dim tmpdate As Date
Dim daycount As Integer
Dim datediffs As Integer
Dim daycounts As Integer
Dim finals As Integer
Dim finaldays As Integer
Dim finalhours As Integer
Dim finalmins As Integer

'### Reset Variables ###

daycount = 0 'Reset daycount to Zero

'### End Reset Variables ###


'### Check if need to only calculate hours ###

'If StartDate and EndDate are the same calculate Hours only
If dtestartdate <> dteenddate Then

    'Add one day onto start date
    tmpdate = dtestartdate + 1

    'if tmpdate is the same as the same as the end, only calculate hours
    If tmpdate <> EndDate Then

'End ### Check if need to only calculate hours ###
     
        Do While tmpdate <> dteenddate

'### Count Non-Working Days ###
            '### Count Weekend days###
            Select Case Weekday(dteTemp)
                Case Is = 1, 7
                     daycount = daycount + 1
                'Count Weekend
                Case Else
                'Do Nothing
            End Select
            

            '### Count Bankholidays ###
            If daycount("[HolidayDate]", "tbl_BankHolidays", "[HolidayDate] = #" & tmpdate & "#") > 0 Then
                daycount = daycount + 1
            End If

        tmpdate = tmpdate + 1

        Loop 'Do While tmpdate <> dteEndDate
'### End Count Non-Working Days ###

    End If 'tmpStartDate = EndDate Then

End If 'dteStartDate = dteEndDate Then


'Convert daycount to seconds

daycounts = daycount * 86400

'### Calculate difference between start and end date ###

datediffs = DateDiff("s", StartDate, EndDate)

'### End Calculate difference between start and end date ###

'### Subtract Non-Working Days ###

finals = datediffs - daycounts

'### End Subtract Non-Working Days ###

'### Format result ###

finaldays = finals / 86400
finalhours = (finals - (finaldays * 86400)) / 3600
finalmins = (finals - (finals * 1400)) / 60

'### End Format Result ###
 
 End Sub

but before I can even test that this is giving the correct result (which I doub - 1st attempt at any significnt VBA) I get a complie error.

At the line 'If Daycount("[HolidayDate]", "tbl_HolidayList", "[Holiday] = #" & tmpDate & "#") > 0 Then' I get the error 'Expected Array'

Help, please

And if you see anything else that needs fixing please do so.

Thanks in advance.

Martin

 

[pbaldy]

Daycount is a variable in your procedure. That appears to be the structure for a DCount. Is that what you meant to have there?

 

[martinjward]

Yep, the variable is Daycount - the only place 'Dcount' apperas is in the notation, not in the actual code.

 

[pbaldy]

What I'm saying is that you probably want to change that to DCount. Daycount(...) will throw an error every time.

 

[martinjward]

As you can see from below I have done that (using a find & replace, but still get the same prolem!

Public Sub DayDifference(dtestartdate As Date, dteenddate As Date)

Dim tmpdate As Date
Dim dcount As Integer
Dim datediffs As Integer
Dim dcounts As Integer
Dim finals As Integer
Dim finaldays As Integer
Dim finalhours As Integer
Dim finalmins As Integer

'### Reset Variables ###

dcount = 0 'Reset dcount to Zero

'### End Reset Variables ###


'### Check if need to only calculate hours ###

'If StartDate and EndDate are the same calculate Hours only
If dtestartdate <> dteenddate Then

    'Add one day onto start date
    tmpdate = dtestartdate + 1

    'if tmpdate is the same as the same as the end, only calculate hours
    If tmpdate <> EndDate Then

'End ### Check if need to only calculate hours ###
     
        Do While tmpdate <> dteenddate

'### Count Non-Working Days ###
            '### Count Weekend days###
            Select Case Weekday(dteTemp)
                Case Is = 1, 7
                     dcount = dcount + 1
                'Count Weekend
                Case Else
                'Do Nothing
            End Select
            

            '### Count Bankholidays ###
            If dcount("[HolidayDate]", "tbl_BankHolidays", "[HolidayDate] = #" & tmpdate & "#") > 0 Then
                dcount = dcount + 1
            End If

        tmpdate = tmpdate + 1

        Loop 'Do While tmpdate <> dteEndDate
'### End Count Non-Working Days ###

    End If 'tmpStartDate = EndDate Then

End If 'dteStartDate = dteEndDate Then


'Convert dcount to seconds

dcounts = dcount * 86400

'### Calculate difference between start and end date ###

datediffs = DateDiff("s", StartDate, EndDate)

'### End Calculate difference between start and end date ###

'### Subtract Non-Working Days ###

finals = datediffs - dcounts

'### End Subtract Non-Working Days ###

'### Format result ###

finaldays = finals / 86400
finalhours = (finals - (finaldays * 86400)) / 3600
finalmins = (finals - (finals * 1400)) / 60

'### End Format Result ###
 
 End Sub

 

[pbaldy]

Well, I doubt it throws the same error, but it will throw an error. You were fine having the variable the way it was. The problem was on that specific line. There is a built in function named DCount. It is built for a specific purpose. Variables are used for a different purpose. You don't want a variable with the same name as the function, as Access will get confused about which one you're referring to.

I'm obviously a fan of getting help from sites like this, since I've been on both sides of the equation. However, rather than just cutting and pasting code, you should try to understand the code you get and use. Doing a global find and replace without understanding why will just get you in trouble in the long run. After all is said and done, you're the one responsible for the code in your application, so you need to understand it.

Step through the code line by line and try to understand what it's going to do (and why). If you're not sure, ask. We'll sort it out.