Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-02-2015, 10:12 AM   #46
msk7777
Newly Registered User
 
Join Date: Jul 2009
Location: Dallas, TX U.S.A.
Posts: 78
Thanks: 14
Thanked 0 Times in 0 Posts
msk7777 is on a distinguished road
Re: Calculating Total Work Hours Between Two DateTime Fields

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!

msk7777 is offline   Reply With Quote
Old 02-05-2019, 01:56 AM   #47
MattBaldry
Newly Registered User
 
Join Date: Feb 2019
Posts: 20
Thanks: 3
Thanked 2 Times in 2 Posts
MattBaldry is on a distinguished road
Re: Calculating Total Work Hours Between Two DateTime Fields

Quote:
Originally Posted by Grumm View Post
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
MattBaldry is offline   Reply With Quote
Old 02-05-2019, 03:40 AM   #48
Grumm
Newly Registered User
 
Join Date: Oct 2015
Location: On a planet in space
Posts: 284
Thanks: 14
Thanked 59 Times in 58 Posts
Grumm is on a distinguished road
Re: Calculating Total Work Hours Between Two DateTime Fields

Quote:
Originally Posted by MattBaldry View Post
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.

Grumm is offline   Reply With Quote
Old 02-05-2019, 03:57 AM   #49
MattBaldry
Newly Registered User
 
Join Date: Feb 2019
Posts: 20
Thanks: 3
Thanked 2 Times in 2 Posts
MattBaldry is on a distinguished road
Re: Calculating Total Work Hours Between Two DateTime Fields

Quote:
Originally Posted by Grumm View Post
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
MattBaldry is offline   Reply With Quote
Old 02-05-2019, 04:15 AM   #50
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,483
Thanks: 378
Thanked 595 Times in 577 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Calculating Total Work Hours Between Two DateTime Fields

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 02-06-2019, 04:12 AM   #51
Grumm
Newly Registered User
 
Join Date: Oct 2015
Location: On a planet in space
Posts: 284
Thanks: 14
Thanked 59 Times in 58 Posts
Grumm is on a distinguished road
Re: Calculating Total Work Hours Between Two DateTime Fields

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.
Grumm is offline   Reply With Quote
Old 02-06-2019, 06:43 AM   #52
MattBaldry
Newly Registered User
 
Join Date: Feb 2019
Posts: 20
Thanks: 3
Thanked 2 Times in 2 Posts
MattBaldry is on a distinguished road
Re: Calculating Total Work Hours Between Two DateTime Fields

Quote:
Originally Posted by Grumm View Post
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 is offline   Reply With Quote
The Following User Says Thank You to MattBaldry For This Useful Post:
Grumm (02-06-2019)
Old 02-25-2019, 08:45 AM   #53
MattBaldry
Newly Registered User
 
Join Date: Feb 2019
Posts: 20
Thanks: 3
Thanked 2 Times in 2 Posts
MattBaldry is on a distinguished road
Re: Calculating Total Work Hours Between Two DateTime Fields

Quote:
Originally Posted by Grumm View Post
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
MattBaldry is offline   Reply With Quote
Old 02-25-2019, 08:59 AM   #54
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,483
Thanks: 378
Thanked 595 Times in 577 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Calculating Total Work Hours Between Two DateTime Fields

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?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 02-26-2019, 12:30 AM   #55
MattBaldry
Newly Registered User
 
Join Date: Feb 2019
Posts: 20
Thanks: 3
Thanked 2 Times in 2 Posts
MattBaldry is on a distinguished road
Re: Calculating Total Work Hours Between Two DateTime Fields

Quote:
Originally Posted by Gasman View Post
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 by MattBaldry; 02-26-2019 at 01:40 AM.
MattBaldry is offline   Reply With Quote
Old 02-26-2019, 03:59 AM   #56
Grumm
Newly Registered User
 
Join Date: Oct 2015
Location: On a planet in space
Posts: 284
Thanks: 14
Thanked 59 Times in 58 Posts
Grumm is on a distinguished road
Re: Calculating Total Work Hours Between Two DateTime Fields

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 by Grumm; 02-26-2019 at 04:59 AM.
Grumm is offline   Reply With Quote
Old 03-06-2019, 06:30 AM   #57
MattBaldry
Newly Registered User
 
Join Date: Feb 2019
Posts: 20
Thanks: 3
Thanked 2 Times in 2 Posts
MattBaldry is on a distinguished road
Re: Calculating Total Work Hours Between Two DateTime Fields

Quote:
Originally Posted by Grumm View Post
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/for....php?p=1614451

Started my own thread on this one now.

~Matt

MattBaldry is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating total daily hours grogmi1 Queries 3 02-18-2014 05:53 AM
Designing Date and Time fields to total up hours sal Forms 3 05-21-2012 10:45 AM
Calculating total number of hours worked hooby1 Reports 11 02-19-2010 12:54 PM
Building a query to pull "Work Shift Hours" out of a total time KNyczaj Queries 4 02-12-2010 07:39 AM
calculating total costs from 2 different fields wilkob Queries 5 12-21-2005 10:20 AM




All times are GMT -8. The time now is 08:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World