Calculate Working hours using DateDiff between 2 different dates and times

[veraloopy]

Hi

I'm trying to calculate the difference in working hours between a start and end date so I can calculate the labour cost for time spent on a job.
I can easily do the calculation if the visit is on the same day, but i'm struggling where the visit may span over a number of days.

Initially I tried having separate start and end date & time fields (txtDateStart, txtDateEnd, txtTimeStart and txtTimeEnd) and calculated the number of days x 8 hours then added in the hours, but I become stuck when the start date/time is 19/10/2011 16:00 and the end is 21/10/2011 10:00 because my time calculations where doing 10:00 - 16:00 which gave the wrong result.

So now I've created 1 field for each date/time with an inpur mask of 99/99/9999\ 00:00;0;_ and tried this code:

Dim datDate1    As Date
Dim datDate2    As Date
Dim dblNumDays  As Double
Dim dblNumHours As Double
Dim dblWorkDaysInHours As Double
Dim dblHoursCheck As Double
Dim dblTotalWorkHours As Double
Dim dblDate1 As Double
Dim dblDate2 As Double
    
datDate1 = Me.Text2
datDate2 = Me.Text0
    
dblDate1 = CDbl(datDate1)
dblDate2 = CDbl(datDate2)
dblNumDays = dblDate2 - dblDate1
dblNumHours = dblNumDays * 24
Me.txtdiff = Abs(dblNumHours) 
 
If dblNumDays > 0 Then
    dblWorkDaysInHours = Round(dblNumDays * 8, 0)  'Based on 8 hours per day worked
    dblHoursCheck = Round(dblNumDays * 16, 0)
    dblTotalWorkHours = dblNumHours - dblHoursCheck
    Me.txtdiff = Round(dblWorkDaysInHours + dblTotalWorkHours, 2)
Else
   Me.txtdiff = Abs(dblNumHours)
End If
End Sub

It seems to work but it knocks a few hours off the actual time... and this is where I'm getting a little stuck

With my start and end dates/times being 20/10/2011 08:00 - 22/10/2011 10:00 it shows the total hours worked as 9 but it should be 10

I really appreciate any help you can give

 

[veraloopy]

I'm one step further now...

Ive done some extensive searching and found an old post by John Big Booty

Dim Period As Double
Period = DateDiff("n", Me.dteStart, Me.dteEnd)
Me.PeriodHours = Int(Period / 60)
Me.PeriodMinutes = (Period / 60 - Int(Period / 60)) * 60
Me.PeriodTime = Int(Period / 60) & ":" & (Period / 60 - Int(Period / 60)) * 60

Ive used this and I now have the correct hours showing but I need to only count 8 hours per day and also avoid counting any public holidays or weekends.

I have created a table [tblNonWorkingDays] where I have added all public bank holidays but failing in google searches on how to reference it and exclude the dates

 

[vbaInet]

With regards your first post, what is the point of dblDate1 and dblDate2 which are declared as double but you're giving them dates?

I've amended your code and gotten rid of some redundancies:

    Dim datDate1    As Date
    Dim datDate2    As Date
    Dim dblNumDays  As Double
    Dim dblNumHours As Double
    Dim dblWorkDaysInHours As Double
    Dim dblHoursCheck As Double
    Dim dblTotalWorkHours As Double
    Dim txtDiff As String
        
    datDate1 = Me.Text2
    datDate2 = Me.Text0
    dblNumDays = DateDiff("d", datDate1, datDate2)
    dblNumHours = dblNumDays * 24
     
    If dblNumDays > 0 Then
        dblWorkDaysInHours = Round(dblNumDays * 8, 0)  'Based on 8 hours per day worked
        dblHoursCheck = Round(dblNumDays * 16, 0)
        dblTotalWorkHours = dblNumHours - dblHoursCheck
        Me.txtDiff = Round(dblWorkDaysInHours + dblTotalWorkHours, 2)
    Else
       Me.txtDiff = Abs(dblNumHours)
    End If

 

[veraloopy]

I think I was playing around with those 2 doubles

Since first posting, I thinK I have opened up a big can of worms for myself as I never took into account the late start in a particular day (each 'day' may not necessarily be 8 hours if the start time is 4pm for example).
I also didnt take into consideration public holidays and weekends....

Something that I thought would be quite easy is now seeming quite a nightmare!

 

[veraloopy]

I've just amended my code to your suggestion but it's returning 16hrs based on start date 20/10/2011 08:00 - 21/10/2011 10:00

 

[vbaInet]

If you search the forum you will find some code to exclude hols and weekends, then you can incorporate that into your code.

 

[vbaInet]

I've just amended my code to your suggestion but it's returning 16hrs based on start date 20/10/2011 08:00 - 21/10/2011 10:00

That was all based on your calculation. I just simply got rid of redundant bits.

 

[veraloopy]

Sorry, the calculation was actually correct

In my head im working out today's hours from 7am but I havent told Access that so it's calculating from midnight

I'm found some code to calculate from 7am-4pm mon-fri but it uses a reference called NetworkDay which seems to be for excel as I havent got it in my references list...

I'll try working on it to see if I can make it work as reading through it, it seems quite logical, just need to figure out this networkday issue.

many thanks for your help though - very much appreciated

 

[veraloopy]

Getting much closer now

I have found the following code which I've added as a module:

Public Function NetWorkhours(dteStart As Date, dteEnd As Date) As Single
Dim intGrossDays As Integer
Dim intGrossHours As Single
Dim dteCurrDate As Date
Dim i As Integer
Dim WorkDayStart As Date
Dim WorkDayend As Date
Dim nonWorkDays As Integer
Dim StartDayhours As Single
Dim EndDayhours As Single
NetWorkhours = 0
nonWorkDays = 0
'Calculate work day hours on 1st and last day
WorkDayStart = DateValue(dteEnd) + TimeValue("07:00am")
WorkDayend = DateValue(dteStart) + TimeValue("04:00pm")
StartDayhours = DateDiff("n", dteStart, WorkDayend)
EndDayhours = DateDiff("n", WorkDayStart, dteEnd)
'adjust for time entries outside of business hours
If StartDayhours < 0 Then
StartDayhours = 0
End If
If EndDayhours > 8 Then
EndDayhours = 8
End If
'Calculate total hours and days between start and end times
intGrossDays = DateDiff("d", (dteStart), (dteEnd))
intGrossHours = DateDiff("n", (dteStart), (dteEnd))
'count number of weekend days and holidays (from a table called "Holidays" that lists them)
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbSaturday) < 3 Then
nonWorkDays = nonWorkDays + 1
Else
If Not IsNull(DLookup("[NonWorkingDay]", "tblNonWorkingDays", "[NonWorkingDay] = #" & Int(dteCurrDate) & "#")) Then
nonWorkDays = nonWorkDays + 1
End If
End If
Next i
'Calculate number of work hours
Select Case intGrossDays
Case 0
'start and end time on same day
NetWorkhours = intGrossHours
Case 1
'start and end time on consecutive days
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours
Case Is > 1
'start and end time on non consecutive days
NetWorkhours = NetWorkhours - (nonWorkDays * 1)
NetWorkhours = (intGrossDays - 1 - nonWorkDays) * 8
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours
End Select
End Function

I then run a query with this in the builder:

CTMins: NetWorkhours([tblFaultsVisits].[VisitDt],[tblFaultsVisits].[VisitEndDt])

Now this gives me exactly what I want, however, it doesnt seem to calculate around my start and end times. It also doesnt pick up any dates for holidays that I have in my tblNonWorkingDays table

Any suggestions?

 

[veraloopy]

I've figured out what is going wrong but not sure where in the code its happening

It does calculate between 07:00 -16:00 on the first day, but if I change the date ranges to 20/10/2011 04:00am - 21/10/2011 08:00, it calculates the hours for yesterday but uses the start time of 04:00am for today's time even though I have specified 08:00 in the field.

 

[vbaInet]

Could you explain very briefly what the logic is?

It should exclude holidays and weekends and return the number of working hours between the two dates? Also, what is the signficance of before and after 4 am or pm?

 

[veraloopy]

Yes, We have 'visits' which could either be a few hours on the same day or take more than 1 day.

So my [VisitDt] field is the start date/time and my [VisitEndDt] is the end date/time

To work out labour costs, I'm trying to calculate the total number of minutes spent on that particular visit so I can work out the financial cost later.

I only want to include from 7am-4pm monday to friday in the calculation

I also need to exclude calculations for dates that appear in my Non-Working Days table [tblNonWorkingDays]

(for reference, I used the 4am just as a test to see if it would ignore from 4am-7am)

 

[vbaInet]

Ok, I understand now. So the total number of hours in a day is 9 hours. I also need two things clarified:

1. For the start date or end date, are users allowed to enter a weekend date or have you got some validation that disallows this?

2. If the start date was "24/10/2011 13:00:00" and end date was "26/10/2011 15:00:00", what would be the total working hours? Would it be:

* 24/10/2011 - 3 hrs
* 25/10/2011 - 9 hrs (because it's a full day)
* 26/10/2011 - 8 hrs

 

[veraloopy]

Ok, I understand now. So the total number of hours in a day is 9 hours. I also need two things clarified:

1. For the start date or end date, are users allowed to enter a weekend date or have you got some validation that disallows this?

2. If the start date was "24/10/2011 13:00:00" and end date was "26/10/2011 15:00:00", what would be the total working hours? Would it be:

* 24/10/2011 - 3 hrs
* 25/10/2011 - 9 hrs (because it's a full day)
* 26/10/2011 - 8 hrs

1) The start and end date can be entered manually so could be any date at all, although we dont work weekends so there shouldnt be any need for anyone to enter a weekend date.

2) Yes that would be the correct calculation based on the hours worked

Many thanks

 

[vbaInet]

1) The start and end date can be entered manually so could be any date at all, although we dont work weekends so there shouldnt be any need for anyone to enter a weekend date.

I propose that you set up a validation for weekends. It will make the code easier and shorter. Besides that it only makes sense to do so because as human beings we are prone to making mistakes, for example entering the wrong date.

Do you know how?

 

[veraloopy]

I propose that you set up a validation for weekends. It will make the code easier and shorter. Besides that it only makes sense to do so because as human beings we are prone to making mistakes, for example entering the wrong date.

Do you know how?

I've done some work with validation in the past but nothing like avoiding weekend dates.. would you be most kind enough to let me know how?

 

[vbaInet]

Ok, in the Before Update event of both textboxes, you can put something like this - I'm using StartDate as an example:

If WeekDay(Me.txtDateStart) = vbSunday or WeekDay(Me.txtDateStart) = vbSaturday Then
    Cancel = True
    MsgBox "Date must be a weekday"
End If