Calculate Time Difference

[crashnburn99]

I would like to start off by saying thank you to everyone using this forum. People here are very knowledgeable and very kind. Hands down the best forum I have visited.

On to the troubles. I am trying to find a way of calculating the difference in time. I have four fields in my table, TimeIn, TimeOut, LunchIn, LunchOut. Each formatted to general time. I have read a lot about this topic but most people are only looking to calculate the difference in two times not four. Any help would be greatly appreciated. If further information is needed I will be more than happy to supply it.

 

[Alansidman]

Are you looking to sum the two differences, ie 8 to noon, and 1 to 5. The net being 8. Is this the type of calculation you are looking to do? Next question: Where? In a query? In a form? In a report?

 

[crashnburn99]

Yes that is exactly what I am trying to accomplish. I would like to have it in a report that is generated at the end of the pay period stating each employee and the total hours that they worked each day and the total for the pay period at the bottom. Thank you Alansidman for your attention on this matter. I greatly appreciate it.

 

[Alansidman]

Is the time out the time at the end of the day? If this is the case then in a new field in a query type the following:

Total Hours: [Yourtablename].[Time Out]- [yourtablename].[Time In] -([YourTableName].[Lunch Out]-[Yourtablename].[Lunch In])

use the query as your record source for your report.

 

[crashnburn99]

Yes it is. I will try your code when I get to work tomorrow. Thank you very much Alan! I truly appreciate your help.

 

[crashnburn99]

I plugged in the formula but it is coming up as Total Hours .41666 and so on. I tried playing with, moving some stuff around but I could not get it to pull up 8 hours. I manually entered in the times on my table to guarantee that it would 8 hours exact. I will keep playing with it and see if I can come up with something. Thank you for getting me on the right track.

 

[crashnburn99]

So after moving some names around and playing with + and - I still could not get it to work. On a whim I tried this formula:

DateDiff("h",[TimeOut],[TimeIn])+DateDiff("h",[LunchOut],[LunchIn])

It worked!!! But still flawed. It only pulls hours, I need hours and minutes. Do know anyway of making that happen??

 

[Alansidman]

Have to run out for a couple of hours, but had a thought. First look at this.

http://www.techonthenet.com/access/functions/date/datediff.php

I'm thinking that you may have to develop an expression that uses DateDiff and calculates minutes. Then another expression to convert to hours with decimals. And then an expression to convert to hours and minutes. Also look at this this as it may be helpful http://www.techonthenet.com/access/functions/date/format.php

Don't have time to work it through now, but will try later, if you haven't solved.

Good luck.

 

[Alansidman]

Take a look at the attached and advise if this meets your requirements.


EDIT: After posting this response, I happened to hit upon this thread in an unrelated search that I thought might be of interest also.

http://www.access-programmers.co.uk/forums/showthread.php?t=147496

 

[crashnburn99]

Took a look at the links and tried to build some new stuff by Formatting but unfortunatly still dead in the water. I tried to add /60 to the end to get decimals but it still is inaccurate. I altered one row of my table so that not all the times were the same and after adding the /60 it still generated the same time across all rows, found that a bit odd. Ill keep trying new stuff. Again Alan I cannot thank you enough for all of your effort.

 

[crashnburn99]

I just noticed your attachment, I will give it a shot.

 

[crashnburn99]

Alan I think you did it!!!! This looks great!! I have one question, but if your tapped out I understand. Is there a way to stop the numbers two places after the decimal? Thank you again Alan, there is no way I could of gotten this done without you.

 

[Alansidman]

I played around with that and could not figure it out. Maybe try putting the minutes portion of the expression inside a Format function? Perhaps you should start a new thread on that issue and someone else will have the answer. It has stumped me. Glad to have been of help. I enjoyed figuring this one out.

 

[crashnburn99]

Alan you are great!!! I will give it a shot.

 

[crashnburn99]

I found an article about rounding. I implemented it in the minutes field and now I get a whole hour and a whole minute.

Minutes: Round(((DateDiff("n",[TimeIn],[TimeOut])-DateDiff("n",[LunchOut],[LunchIn]))/60-Int((DateDiff("n",[TimeIn],[TimeOut])-DateDiff("n",[LunchOut],[LunchIn]))/60))*60,1)

For future refrences this is what I did. Thanks again Alan this has been a great learning experience.