gasma, what dates?
I have no idea what I am doing wrong, I get 3 hours...below is the query and if I did not change the "12pm", "1pm", I get 2 hours, with the change I get 3 hours.
SELECT tblMonarchPerformance.MPID, tblMonarchPerformance.MPDateExtractStarted, tblMonarchPerformance.MPDateExtractFinished, WorkHrs([MPDateExtractStarted],[MPDateExtractFinished],"8 am","5 pm","12 pm","12 pm") AS WorkHours
FROM tblMonarchPerformance LEFT JOIN tblMonarchCustomer ON tblMonarchPerformance.MPCustID = tblMonarchCustomer.CustID;
My Start time is 7/29/2019 2:00PM and End time is 7/30/2019 9:00AM, and I pasted the WorkHrs code above just as it is...so I am at a loss. So sorry...but you get 4...:banghead:
Thank you.
gasma, the code is long, I may have to trim it down.
there are redundant code, which need to be moved to separate functions/sub.
its not messenger so we cannot see who's typing, so there are gaps in our responses.
there is new code I posted (the last one). the mysterious "missing 1" has been solved there.
? workhrs1(#29/07/2019 2 pm#,#30/07/2019 9 am#,,,"12 pm","1 pm")
4
? workhrs1(#29/07/2019 2 pm#,#30/07/2019 9 am#)
4
I've already told you, if you use the same time for lunch start and end, you will get no lunch deducted.?I see, thank you. I did not know if it was hard to tweak the code you have here to not include a break and making the work day 8am to 4pm with no break? You certainly have gone above and beyond and I hate to ask you for that, is that a lot more trouble?
Lilly
? workhrs(#8/1/2019 9:00am#,#8/2/2019 1pm#)
11
? workhrs(#8/1/2019 9:00am#,#8/2/2019 1:00 pm#,,,"12 pm","12 pm")
12
? workhrs1(#8/1/2019 9:00am#,#8/2/2019 1pm#)
11
? workhrs1(#8/1/2019 9:00am#,#8/2/2019 1:00 pm#,,,"12 pm","12 pm")
11
Hi Lilly. Sorry for jumping back in... I have been following this thread but have not said anything because Arnel is already doing a good job here. However, I am not sure I understand your current issue. If your working hours are from 8AM to 5PM, then here's what I would expect:Yet on 8/1/2019 8AM to 8/2/2019 9AM, I get 9 hours which is correct...so I don't understand...sorry, and again, thanks for the help.
Hi Lilly. Can you please verify for me what are the business hours? Does the work day start at 8AM and ends at 5PM? If so, the time difference between those two is 9 hours. This means if you want to consider that day to be an 8 hour workday, then you'll have to take away a 1 hour break. The problem with that is which "hour" is the break? For example, if a person comes in at 9AM and leaves at 5PM, that's a total of 8 hours, if we "assume" from 8AM to 9AM can be considered as the "break." But, if the break time is any other time than that, then that person only worked 7 hours that day. The same goes if a person leaves early. So, I think for Arnel to modify his code to accommodate your requirements, we may need a better definition of "break" or adjust the business hours to only have 8 hours in a day. For example, start at 8AM and end at 4PM or start at 9AM and end at 5PM.Hello there, oh please don't apologize for stepping in, I appreciate all the help I can get...
So, I don't want to consider breaks, just an 8 hour day...and with the code and just changing the break to 12pm, 12pm I am getting 9 hours for the dates below which is 8 hours the 1st day (no break) and 1 hour the next day for a total time of 9 hours and is correct...does this answer your question? But for one of the dates (above) it appears to be adding the break in...that is what we are trying to figure out.
I hope I explain this.
Lilly