How to Find Total Working Hours Between Dates? (1 Viewer)

JithuAccess

Member
Local time
Today, 04:58
Joined
Mar 3, 2020
Messages
297
Hello Guys,

This is my Table Design:

1606150534657.png


Format for "NC Review Start Time" and NC Review End Time" is Medium Time:

1606150625649.png


and Format for "NC Review Start Date" and NC Review End Date" is General Date :

1606150697937.png

This is the value in my Table:

1606150735454.png


I create a query like this:

1606150814644.png



and this is my Query Result:

1606150888004.png


But total hours worked was 16:00 Hours (8:00 Hours on 23-Nov-2020 and 8:00 Hours on 24-Nov-2020)

Could you guys please guide me what are the changes I have to make in the Query

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:58
Joined
Oct 29, 2018
Messages
21,357
Hi. Just a quick comment, you can reduce your field quantity in half by simply having a Review Start and End fields that store both the date and time together.
 

plog

Banishment Pending
Local time
Today, 05:58
Joined
May 11, 2011
Messages
11,611
First as theDBguy said, you need store your DateTime values in just 1 datetime field. Omit the [NC ...Date] fields and store everything in the [NC ...Time] fields. That way you will be able to use the DateTime functions inherent to access (https://www.techonthenet.com/access/functions/)

Second, only use alphanumeric characters in table and field names. That means no spaces. Doing that makes coding and querying easier.

Lastly, we need more rules. Between 2020-11-23 9:00 AM and 2020-11-24 5:00 PM there are 32 actual hours. You expect your query to return 16 hours. Please explain the exact rules in excluding any hours that shouldn't be counted. What days of the week? What times of day? What holidays? Any other rules that go into this.
 

JithuAccess

Member
Local time
Today, 04:58
Joined
Mar 3, 2020
Messages
297
Hi. Just a quick comment, you can reduce your field quantity in half by simply having a Review Start and End fields that store both the date and time together.
Thanks a lot for your suggestion. I made the changes
 

JithuAccess

Member
Local time
Today, 04:58
Joined
Mar 3, 2020
Messages
297
First as theDBguy said, you need store your DateTime values in just 1 datetime field. Omit the [NC ...Date] fields and store everything in the [NC ...Time] fields. That way you will be able to use the DateTime functions inherent to access (https://www.techonthenet.com/access/functions/)

Second, only use alphanumeric characters in table and field names. That means no spaces. Doing that makes coding and querying easier.

Lastly, we need more rules. Between 2020-11-23 9:00 AM and 2020-11-24 5:00 PM there are 32 actual hours. You expect your query to return 16 hours. Please explain the exact rules in excluding any hours that shouldn't be counted. What days of the week? What times of day? What holidays? Any other rules that go into this.
Thanks a lot for your reply. The rules are like in a Day the Total Work Hour is 7:15 Hours From Monday to Friday. So in the above Example, the Total Hours should show as 7:15 + 7:15 = 14:30.

Thanks
 

plog

Banishment Pending
Local time
Today, 05:58
Joined
May 11, 2011
Messages
11,611
That made it worse. In your initial post there were 8 work hours in a day, now we are at 7.25.

Further you provided no starting nor ending time for counting hours which means if someone starting working at 4:45 pm on one day and ended working at 7:15am the next the query should return 14.5 working hours for them. Is that correct?

Technical problems require technical details. If you need us to help with this you really need to sit down and think about the rules that go into this and provide them to us.
 

JithuAccess

Member
Local time
Today, 04:58
Joined
Mar 3, 2020
Messages
297
That made it worse. In your initial post there were 8 work hours in a day, now we are at 7.25.

Further you provided no starting nor ending time for counting hours which means if someone starting working at 4:45 pm on one day and ended working at 7:15am the next the query should return 14.5 working hours for them. Is that correct?

Technical problems require technical details. If you need us to help with this you really need to sit down and think about the rules that go into this and provide them to us.
Please accept my apology for making it worse. Let me explain you how this system works.

I guess this process is not that much complicated. Let us take a simple Example. A staff has open a file for Review on 23-Nov-2020. His work starts at 8:15 AM he works till 12:15 PM and then go for his 1 Hour Break. He return to his desk at 1:15 PM and work till the End of the Day i.e. 4:30 PM. So on 23-Nov-2020 He worked 4:00 Hours + 3:15 Hours 7:15 Hours. Now I am going to store this details as below in my Table

1606154673873.png


So in this case, I want to create a query which find the difference between 2020-11-23 12:15:00 PM and 2020-11-23 8:15:00 AM which is 4:00 Hours and also the Difference Between 2020-11-23 4:30:00 PM and 2020-11-23 1:15:00 PM which is 3:15 Hours. This process will go till the Staff completed the Review and move that file in to the next Level. Let us assume that staff took 2 weeks to finish the file. So I will be entering the data in the Table like this:

1606155771647.png


I want to just find the correct query to how to find the Time Difference between 2020-11-23 12:15:00 PM and 2020-11-23-8:15:00 AM.

Thanks a lot for your effort and time.
 

Attachments

  • 1606155592738.png
    1606155592738.png
    28 KB · Views: 237

JithuAccess

Member
Local time
Today, 04:58
Joined
Mar 3, 2020
Messages
297
That made it worse. In your initial post there were 8 work hours in a day, now we are at 7.25.

Further you provided no starting nor ending time for counting hours which means if someone starting working at 4:45 pm on one day and ended working at 7:15am the next the query should return 14.5 working hours for them. Is that correct?

Technical problems require technical details. If you need us to help with this you really need to sit down and think about the rules that go into this and provide them to us.

I have created a query like this:

1606156234716.png



I am getting the Result like:

1606156218333.png



We are almost there. But I am not sure why instead of 4:00 and 3:15 we are getting 4:08 and 3:36

Thanks
 

plog

Banishment Pending
Local time
Today, 05:58
Joined
May 11, 2011
Messages
11,611
The latest example you gave had no rules, you simply want to find the difference in hours between two datetimes. That's very easy in Access, you use the DateDiff function:


Your query field could be this:

DateDiff ("h", [NC Review Start Time], [NC Review End Time])

That will give you the difference in hours between the two fields. Now, there are 60 minutes in an hour, not 100. Therefore 7.1 hours is equivalent to 7:06 in hours:minutes format. To achieve a format of hours:minutesthat you are going to need to use DateDiff to calculate minutes difference and then determine how many hours that is and how many remainder minutes.
 

JithuAccess

Member
Local time
Today, 04:58
Joined
Mar 3, 2020
Messages
297
The latest example you gave had no rules, you simply want to find the difference in hours between two datetimes. That's very easy in Access, you use the DateDiff function:


Your query field could be this:

DateDiff ("h", [NC Review Start Time], [NC Review End Time])

That will give you the difference in hours between the two fields. Now, there are 60 minutes in an hour, not 100. Therefore 7.1 hours is equivalent to 7:06 in hours:minutes format. To achieve a format of hours:minutesthat you are going to need to use DateDiff to calculate minutes difference and then determine how many hours that is and how many remainder minutes.

Thanks a lot for your kind help. Now I am getting like this:

1606157596151.png


What changes in the query I should make to get like:

1606157645820.png

So sorry for bothering you again and again.

Thanks
 

JithuAccess

Member
Local time
Today, 04:58
Joined
Mar 3, 2020
Messages
297
The latest example you gave had no rules, you simply want to find the difference in hours between two datetimes. That's very easy in Access, you use the DateDiff function:


Your query field could be this:

DateDiff ("h", [NC Review Start Time], [NC Review End Time])

That will give you the difference in hours between the two fields. Now, there are 60 minutes in an hour, not 100. Therefore 7.1 hours is equivalent to 7:06 in hours:minutes format. To achieve a format of hours:minutesthat you are going to need to use DateDiff to calculate minutes difference and then determine how many hours that is and how many remainder minutes.

When I give "n" (minutes) instead of "h" and then divided this by 60 (converting hours to minutes) my result is like this:

1606158190730.png


1606158205173.png


Now I want to just change this 3:25 in to 3:15.

Thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:58
Joined
Feb 28, 2001
Messages
26,996
I'm going to step away from code and give you a little theory to explain what is going on.

Access treats DATE fields as what is called a CAST or TYPECAST of the DOUBLE data type. The unit of the DATE field is DAYS. Time of day becomes a fraction of a day, so your hours and minutes are simply fractions less than 1. When you do the computation for elapsed time, you can subtract date/time fields directly and will get fractions of a day. Multiply by 24 to get hours. When you see the totals like 4.08 or 3.36, those are not hours and minutes but hours and fractions of hours.

You can supply this difference to the FORMAT function with a specification of "hh:nn" for any single difference. However, the problem will become ugly when you consider sums of individual differences that in aggregate total more than 24 yours. Using "ordinary" Access formatting, you will start seeing strange dates in January of 1900. If the Access formatting routine has any serious failing, it is that you cannot specify "hhh:nn" to give you hours and minutes that exceed 24 hours. (Some other versions of BASIC actually DO allow that format. VBA does not.)

Therefore, the simplest technique I would recommend is to leave dates as they are and only format the differences. Roll your own function to show hours greater than 24. We have quite a few "custom" time format routines that you could find with a short search.

OR never leave things as raw dates once you have a difference in hours; convert them to floating-point hours (i.e. multiply the sum by 24) and always work with them from that viewpoint.

Do one or the other. Maintain consistency in what you do so that you can avoid confusion. Myself, I would work in date/time variables as a PERSONAL preference because formatting isn't that hard. But you have to do what is right for you.
 

Users who are viewing this thread

Top Bottom