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

Grumm

Registered User.
Local time
Today, 17:32
Joined
Oct 9, 2015
Messages
395
Ok that can be hard to do.
I will do the formatting in the function for you so the result will be a string "hh:mm:ss"

Code:
Public Function NetWorkHours(dteStart As Date, dteEnd As Date) As String
    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 lHours as long
    Dim lMinutes 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
    lHours = Int(Result / 60)
    lMinutes = Format(Result - (lHours * 60), "00")
    NetWorkHours = lHours & ":" & lMinutes & ":00"
End Function

(I couldn't test it since i'm not at work anymore. But give it a try. I will check tomorrow if there are problems with it.
 

msk7777

Registered User.
Local time
Today, 09:32
Joined
Jul 17, 2009
Messages
78
PERFECTION! That did the trick with no problems! You are a life saver! Thank you so much for sticking with me and helping figure it out. I now don't have to tell my boss that I can't give them what they want!

Ok that can be hard to do.
I will do the formatting in the function for you so the result will be a string "hh:mm:ss"

Code:
Public Function NetWorkHours(dteStart As Date, dteEnd As Date) As String
    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 lHours as long
    Dim lMinutes 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
    lHours = Int(Result / 60)
    lMinutes = Format(Result - (lHours * 60), "00")
    NetWorkHours = lHours & ":" & lMinutes & ":00"
End Function
(I couldn't test it since i'm not at work anymore. But give it a try. I will check tomorrow if there are problems with it.
 

Grumm

Registered User.
Local time
Today, 17:32
Joined
Oct 9, 2015
Messages
395
Glad it worked and that you could get something useful to your boss.
 

msk7777

Registered User.
Local time
Today, 09:32
Joined
Jul 17, 2009
Messages
78
Grumm - Got a quick question. Does your code turn the time result into text by chance? I only ask because I am creating a parameter query off of this query that sums the Workedhours field by RequestType

1st Query:
Code:
SELECT RequestTable.RequestID, RequestTable.RequestType, RequestTable.RequestTitle, RequestTable.ReceivedDateTime, RequestTable.ActualCompletionDateTime, IIf([RequestTable]![RequestStatus]="Completed",NetWorkDays([RequestTable]![ReceivedDateTime],[RequestTable]![ActualCompletionDateTime]),[RequestTable]![RequestStatus]) AS WorkDays, IIf([RequestTable]![RequestStatus]="Completed",NetWorkHours([RequestTable]![ReceivedDateTime],[RequestTable]![ActualCompletionDateTime]),0) AS WorkTime
FROM RequestTable;

Here is the parameter query:

Code:
SELECT DISTINCT WorkingHoursQuery.RequestType, Sum([WorkingHoursQuery]![WorkTime]) AS WorkTime
FROM WorkingHoursQuery
WHERE (((WorkingHoursQuery.RequestType)="Fee Schedule"))
GROUP BY WorkingHoursQuery.RequestType;

The parameter query keeps giving me a data mismatch error which made me think that the time may actually be in text format.

So sorry to bother you again!

Glad it worked and that you could get something useful to your boss.
 

Grumm

Registered User.
Local time
Today, 17:32
Joined
Oct 9, 2015
Messages
395
Yes it is a text. I did that in order to get you that "hh:mm:ss" format.
It will be hard to make a sum of it. Maybe it is possible but we will have to remove that string part and make the result in minutes again. Then you can make the sum of it. After that you need to convert it back into "hh:mm:ss"

Maybe someone else has a quick solution, but I would create vba code to do all this. Formatting directly in SQL query's is a pain...
 

msk7777

Registered User.
Local time
Today, 09:32
Joined
Jul 17, 2009
Messages
78
I can try to research a solution. I just wanted to verify that it was text. That at least gives me a starting point. Thanks Grumm!
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 16:32
Joined
Feb 5, 2019
Messages
292
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 :)

Not sure if Grumm is still active but I have a few questions with regards to this code if he is still around?

~Matt
 

Grumm

Registered User.
Local time
Today, 17:32
Joined
Oct 9, 2015
Messages
395
Not sure if Grumm is still active but I have a few questions with regards to this code if he is still around?

~Matt

You can ask the questions all the time :)
Maybe someone else than me can answer it.
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 16:32
Joined
Feb 5, 2019
Messages
292
You can ask the questions all the time :)
Maybe someone else than me can answer it.

I was wondering how I could use your code but have different working hours. At my work, production works 07:30 to 16:00 Monday to Thursday and 07:30 to 13:00 on Friday. The code works perfect and if the Friday hours could not be added, that is fine. But if it could....that would be perfect :)

I am self taught so I am reading the code and trying to understand it to make the edits myself too, but no luck yet.

~Matt
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:32
Joined
Sep 21, 2011
Messages
14,044
I would probably put the start and end times into variables and use them instead of hardcoded strings.

Then I would test for a Friday and adjust them as needed.?

HTH
 

Grumm

Registered User.
Local time
Today, 17:32
Joined
Oct 9, 2015
Messages
395
The code was made 3 years ago. I just started to learn access stuff...
It needs indeed some extra work to make it pretty and more generic.
The code was for a specific need of the person who asked for it.
I will try a more elegant solution for you...

Basically you need to add an extra condition when it is a Friday, you add less minutes.
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 16:32
Joined
Feb 5, 2019
Messages
292
The code was made 3 years ago. I just started to learn access stuff...
It needs indeed some extra work to make it pretty and more generic.
The code was for a specific need of the person who asked for it.
I will try a more elegant solution for you...

Basically you need to add an extra condition when it is a Friday, you add less minutes.

I am going to start my own thread because I think there may be a more efficient way for me to get my data.

~Matt
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 16:32
Joined
Feb 5, 2019
Messages
292
The code was made 3 years ago. I just started to learn access stuff...
It needs indeed some extra work to make it pretty and more generic.
The code was for a specific need of the person who asked for it.
I will try a more elegant solution for you...

Basically you need to add an extra condition when it is a Friday, you add less minutes.

I have my data and it is all working but I need to find a way to remove tea break, (10:00 - 10:10), lunch break (13:00 - 13:30) and Friday afternoons (13:00 - 16:00).

Are you able to offer any advice?

~Matt
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:32
Joined
Sep 21, 2011
Messages
14,044
All you need to do is subtract the minutes for the breaks.?
As you would have to test for friday for 1 to 4pm, you may as well just test as mentioned before and ignore that period altogether?

So just take off that 40 minutes each day and only 10 on fridays.?

What happens if someone like me works through his lunch though?
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 16:32
Joined
Feb 5, 2019
Messages
292
All you need to do is subtract the minutes for the breaks.?
As you would have to test for friday for 1 to 4pm, you may as well just test as mentioned before and ignore that period altogether?

So just take off that 40 minutes each day and only 10 on fridays.?

What happens if someone like me works through his lunch though?

The people here do not work through their lunch breaks, that would be insane ;-)

Also, not every job will work through a lunch or tea break, so removing 40 minutes each time would not work. I need to be able to get the code to remove the actual non-working times.

~Matt
 
Last edited:

Grumm

Registered User.
Local time
Today, 17:32
Joined
Oct 9, 2015
Messages
395
The code is based on a x hour day. So if you have less, then change it to less.
Also note that what you ask is a little different. I made this with the following in mind : "(work hours are 08:00:00 AM to 17:00:00 PM each day)"
So 5 days a week same hours.
You will have to change it to add only 4 hours on all the Fridays. (You can enter it in plain minutes if that makes it easier for you)

I suggest to make a new thread. Mentioning this one and explain what you need. That way i can ask you more information.
Like adding a few parameters like break times and more.
 
Last edited:

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 16:32
Joined
Feb 5, 2019
Messages
292
The code is based on a x hour day. So if you have less, then change it to less.
Also note that what you ask is a little different. I made this with the following in mind : "(work hours are 08:00:00 AM to 17:00:00 PM each day)"
So 5 days a week same hours.
You will have to change it to add only 4 hours on all the Fridays. (You can enter it in plain minutes if that makes it easier for you)

I suggest to make a new thread. Mentioning this one and explain what you need. That way i can ask you more information.
Like adding a few parameters like break times and more.

https://access-programmers.co.uk/forums/showthread.php?p=1614451

Started my own thread on this one now.

~Matt
 

Users who are viewing this thread

Top Bottom