Calculating Total Work Hours Between Two DateTime Fields (1 Viewer)

msk7777 · Nov 25, 2015

Whatever the issue is keeps pointing to the "Result = Result + MinDay" of this section:

Code:

Do Until StDateD = EnDateD
            'If the date is not a saterday or a sunday we add one day.
            If (Weekday(StDateD) > 1) And (Weekday(StDateD) < 7) Then
                Result = Result + MinDay
                'Check for the holiday. If the date is a holiday, then we remove one day
                If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(StDateD) & "#")) Then
                  Result = Result - MinDay
                End If

msk7777 said:
I am also getting a Runtime Error '6': Overflow and on debugging it highlights:

Result = Result + MinDay

Cronk · Nov 25, 2015

The number of hours is
CalculatedMinutes\60

The number of minutes is
CalculatedMinutes - 60 * (CalculatedMinutes\60)

You cannot use format(xxx,"hh:nn") to get an accurate result for anything more than 24 hours.

msk7777 · Nov 25, 2015

Ok I understand where you are going now. However, I have to show total worked hours, not days. This goes to my boss and they do everything by hours. So how else would you suggest I get the total hours when they go past 24 hours?

Cronk · Nov 26, 2015

I got misled by your dividing by 24 and 60 in post #16 because the 24 is the number of hours in a day.

As to your problem with Result = Result + MinDay
MinDay is the daily work minutes of 8 hours * 60

Substitute 480 for MinDay.

This really is a simple exercise.
If the start day is the same as the end day, calculate the minutes worked that day.
If the start day is not the same as the end day, calculate the minutes worked on both the start and end days, and add 480 for each work day in between (ie skip Sat, Sun and holidays).
Then divide the result by 60 to get the number of hours.

Grumm · Nov 26, 2015

the start date is 05/18/2015 12:21 PM and end date is 8/15/2015 5:00 PM

That gives me 524h and 39m (around 65 days). So that looks correct to me.
You can't divide the number of hours by 24. Since we calculated the time based on 8 hour days. Doing 524/24 = 21 days. So in total he worked 21 days non stop on that project . (24h days without sleep)

My function returns minutes. That gives you control on how you want to display the times. If it still gives wrong times, I suggest to get someone with US time notation to debug the code and see if there is any conversion that go wrong. Like I said, for me with dd/mm/yyyy the function is working correctly.

msk7777 · Nov 30, 2015

Grumm - In the query I removed the "/24" and it does look closer but still not right. For the same record you reference above it now shows 15:36:00 (which is higher than more than the previous 04:39:00).

Cronk - I did toy with changing the MinDay for 480, however it didn't help. I believe Grumm may be correct and it's a time conversion issue.

If anyone could help me finalize this, again, I am needing to display total worked hours. If a project took 3 months, and let's say 100 hours, I need it to display 100:00:00. Thanks in advance and sorry to be so much trouble!

Grumm · Nov 30, 2015

Well 8x60 = 480... that is actually the minutes that I add for 1 hole day working on the project.

I will try to change my timezone to check what is going wrong. As you can see, the examples I run are pretty accurate. (+- 60 seconds).
You want hours so all you have to do is divide the result by 60. (see my post #9 where i convert it to hh:mm)

msk7777 · Nov 30, 2015

Thanks Grumm. yea I have the query dividing by 60 only, I dropped the "/24". It helped but somehow we are just a tad off. I am trying to educate myself as we go along but still not coming up with much. I truly appreciate any help you can provide!

Grumm · Dec 1, 2015

I think the format may give you a wrong result.
Can you remove the format and the /60 ? Then let us know what value you get ?
I suspect that you can't divide minutes by 60 then format that result into "hh:mm"
(I may be wrong and that some magic Microsoft process can make this happen)

Cronk · Dec 1, 2015

Indeed you cannot use format in this context. Dates are stored as double numbers where the value in front of the decimal is the number of days since Dec 31 1899 and the decimal part representing the fraction of the day
format(1.5, "hh:nn") gives 12:00 (ie midday)
format(0.75, "hh:nn") gives 18:00
format(400.25, "hh:nn") gives 06:00

If total minutes is held in a variable lngTotMins, then
the number of hours is lngTotMins\60 and the number of minutes is
lngTotMins - 60 * ( lngTotMins\60)

msk7777 · Dec 1, 2015

ok I also removed the "/60" from the query, but now I get all "00:00:00".

Grumm · Dec 1, 2015

Did you removed the format also ?
You should end with something like this :

Code:

WorkHours: IIf([RequestTable]![RequestStatus]="Completed",NetWorkHours([RequestTable]![ReceivedDateTime],[RequestTable]![ActualCompletionDateTime]),[RequestTable]![RequestStatus])

msk7777 · Dec 1, 2015

I did both but now I just get whole numbers. For the same record we have been previously referencing, the worked hours totals "30519".

Just to give you a few options...If I add back the "/60" I get the following "508.65"

This is the query formula:

Code:

WorkHours: IIf([RequestTable]![RequestStatus]="Completed",NetWorkHours([RequestTable]![ReceivedDateTime],[RequestTable]![ActualCompletionDateTime])/60,[RequestTable]![RequestStatus])

I also want to add that in all scenarios I am still getting the "Run-time error '6 and in debug it points to the "Result = Result + MinDay" (also does it if I change it to "Result = Result + 480").

Grumm said:
Did you removed the format also ?
You should end with something like this :

Code:

WorkHours: IIf([RequestTable]![RequestStatus]="Completed",NetWorkHours([RequestTable]![ReceivedDateTime],[RequestTable]![ActualCompletionDateTime]),[RequestTable]![RequestStatus])

Grumm · Dec 1, 2015

That is pretty close to what i get. They only thing I can think of now is that there are 2 holidays for you between the 2 dates.

To get the correct hh:mm you need to do what Cronk said :

If total minutes is held in a variable lngTotMins, then
the number of hours is lngTotMins\60 and the number of minutes is
lngTotMins - 60 * ( lngTotMins\60)

msk7777 · Dec 1, 2015

Ok, If necessary I can just remove the holiday portion. It's not that big of a deal. However, I don't quite understand how to implement Cronk's suggestion.

Grumm said:
That is pretty close to what i get. They only thing I can think of now is that there are 2 holidays for you between the 2 dates.

To get the correct hh:mm you need to do what Cronk said :

Grumm · Dec 1, 2015

What i mean is that my result is different of yours because of the holiday. When i tested the code, i don't have a holiday table. So i ran the code without it.

The code of Cronk allows you to convert minutes to hours:minutes.
I guess you try to display in a table form the data ? If yes, i suggest you add a calculated field there and not in the query.
The query gives you the result of that function. And in your form, you make a field for the hours and one for the minutes. Hope that helps.

msk7777 · Dec 1, 2015

Just to test if it was the Holiday list giving me the problem I removed the IF statement:

Code:

'Check for the holiday. If the date is a holiday, then we remove one day
                If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #"  & Int(StDateD) & "#")) Then
                  Result = Result - MinDay
                End If

I was able to scroll through the query results a lot further than before but eventually got the error again and it pointed to the same Result= line.

At this point I am just lost as to what to do to get the results I need. I truly appreciate you and Cronk's help and sorry your efforts aren't working. I am sure your efforts are correct and I am probably not applying them correctly on my end.

I will probably just have to tell my boss I can't get the results they are asking for right now but will continue working on it.

Grumm said:
What i mean is that my result is different of yours because of the holiday. When i tested the code, i don't have a holiday table. So i ran the code without it.

The code of Cronk allows you to convert minutes to hours:minutes.
I guess you try to display in a table form the data ? If yes, i suggest you add a calculated field there and not in the query.
The query gives you the result of that function. And in your form, you make a field for the hours and one for the minutes. Hope that helps.

msk7777 · Dec 1, 2015

UPDATE: I figured out the cause of the error, there was bad data in the table. Someone input an 8:00 PM instead of 8:00 AM and it was making the scripting go nuts.

Grumm · Dec 1, 2015

Ok, I understand now.

Try this one :

Code:

Public Function NetWorkHours(dteStart As Date, dteEnd As Date) As Long
    Dim StDate As Date
    Dim StDateD As Date
    Dim StDateT As Date
    Dim EnDate As Date
    Dim EnDateD As Date
    Dim EnDateT As Date
    Dim WorkDay1Start As Date
    Dim WorkDay1end As Date
    Dim Result As Long
    Dim MinDay As Integer
  
    StDate = CDate(dteStart)
    EnDate = CDate(dteEnd)
    
    WorkDay1Start = DateValue(StDate) + TimeValue("08:00:00")
    WorkDay1end = DateValue(StDate) + TimeValue("17:00:00")

    StDateD = CDate(Format(StDate, "Short Date"))
    EnDateD = CDate(Format(EnDate, "Short Date"))

    If StDateD = EnDateD Then
      Result = DateDiff("n", StDate, EnDate, vbUseSystemDayOfWeek)
    Else
        MinDay = (8 * 60) 'Number of minutes of a working day. Change this if you change the start and end times.
        
        'Extract the time from the two timestamps
        StDateT = Format(StDate, "Short Time")
        EnDateT = Format(EnDate, "Short Time")
'
        'Calculate the minutes of the first day and the second one. Don't know what to do yet if the start is after 5pm or the end is before 8am
        Result = DateDiff("n", StDateT, TimeValue("17:00:00"), vbUseSystemDayOfWeek)
        Result = Result + DateDiff("n", TimeValue("08:00:00"), EnDateT, vbUseSystemDayOfWeek)
        
        'Add 1 day to start date. This is to start the loop to get all the days between both dates.
        StDateD = DateAdd("d", 1, StDateD)
        
        Do Until StDateD = EnDateD
            'If the date is not a saterday or a sunday we add one day.
            If (Weekday(StDateD) > 1) And (Weekday(StDateD) < 7) Then
                Result = Result + MinDay
                'Check for the holiday. If the date is a holiday, then we remove one day
                If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(StDateD) & "#")) Then
                  Result = Result - MinDay
                End If
          End If
          StDateD = DateAdd("d", 1, StDateD)
        Loop
    End If
    NetWorkHours = Result
End Function

(you can remove the holidays if you want.)
What I suspect is that you have big projects. And integers are 16 bits (max 32767)
I changed it to long so that you can go to around 3 million hours.
Give it quick try and let us know

msk7777 · Dec 1, 2015

Ok I made the change, it seems better. I just can't figure out hour to convert it to hh:nn:ss AND sum the hours greater than 24 hours. I tried formatting your new coding and the highest total hours in the results is 23:36:00.

Grumm said:

Ok, I understand now.

Try this one :

Code:

Public Function NetWorkHours(dteStart As Date, dteEnd As Date) As Long
    Dim StDate As Date
    Dim StDateD As Date
    Dim StDateT As Date
    Dim EnDate As Date
    Dim EnDateD As Date
    Dim EnDateT As Date
    Dim WorkDay1Start As Date
    Dim WorkDay1end As Date
    Dim Result As Long
    Dim MinDay As Integer
  
    StDate = CDate(dteStart)
    EnDate = CDate(dteEnd)
    
    WorkDay1Start = DateValue(StDate) + TimeValue("08:00:00")
    WorkDay1end = DateValue(StDate) + TimeValue("17:00:00")

    StDateD = CDate(Format(StDate, "Short Date"))
    EnDateD = CDate(Format(EnDate, "Short Date"))

    If StDateD = EnDateD Then
      Result = DateDiff("n", StDate, EnDate, vbUseSystemDayOfWeek)
    Else
        MinDay = (8 * 60) 'Number of minutes of a working day. Change this if you change the start and end times.
        
        'Extract the time from the two timestamps
        StDateT = Format(StDate, "Short Time")
        EnDateT = Format(EnDate, "Short Time")
'
        'Calculate the minutes of the first day and the second one. Don't know what to do yet if the start is after 5pm or the end is before 8am
        Result = DateDiff("n", StDateT, TimeValue("17:00:00"), vbUseSystemDayOfWeek)
        Result = Result + DateDiff("n", TimeValue("08:00:00"), EnDateT, vbUseSystemDayOfWeek)
        
        'Add 1 day to start date. This is to start the loop to get all the days between both dates.
        StDateD = DateAdd("d", 1, StDateD)
        
        Do Until StDateD = EnDateD
            'If the date is not a saterday or a sunday we add one day.
            If (Weekday(StDateD) > 1) And (Weekday(StDateD) < 7) Then
                Result = Result + MinDay
                'Check for the holiday. If the date is a holiday, then we remove one day
                If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(StDateD) & "#")) Then
                  Result = Result - MinDay
                End If
          End If
          StDateD = DateAdd("d", 1, StDateD)
        Loop
    End If
    NetWorkHours = Result
End Function

(you can remove the holidays if you want.)
What I suspect is that you have big projects. And integers are 16 bits (max 32767)
I changed it to long so that you can go to around 3 million hours.
Give it quick try and let us know

Calculating Total Work Hours Between Two DateTime Fields (1 Viewer)

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread