How to Find Total Working Hours Between Dates?

[JithuAccess]

Hello Guys,

This is my Table Design:

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

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

This is the value in my Table:

I create a query like this:

and this is my Query Result:

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]

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]

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]

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]

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]

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]

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

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:

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.

 

[JithuAccess]

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:

I am getting the Result like:

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]

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:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

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]

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:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

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:

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

So sorry for bothering you again and again.

Thanks

 

[JithuAccess]

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:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

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:

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

Thanks

 

[theDBguy]

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

Try something like:

Int(3.25) & ":" & (3.25-Int(3.25))*60

 

[The_Doc_Man]

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.