Code for Business Hours -Help Please (1 Viewer)

[Gasman]

arne,
I got the correct values with your existing code?

 

[arnelgp]

if you don't want the other parameters (work start, work end, break time start... etc.)
but just the two dates (start and end), then you create another function that calls the function I made:

public function myWorkHours(date1 as date, date2 as date) as double
myWorkHours = WorkHrs(date1, date2)
end function

you then use "myWorkHours" on your query instead of the orig.

 

[arnelgp]

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.

 

[Lilly420]

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.

 

[Gasman]

gasma, what dates?

His last line of dates and times.
See my posts just before your replies today.

 

[Gasman]

Please show the actual output of the query.
To simplify matters, you can exclude the last 4 parameters?

Also execute the function in the immediate window as I have done.

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.

 

[Gasman]

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.

Perhaps, but I believe it is working correctly?, at least with the dates and times I tested, which are the same as those of the o/p. The only difference I can see is I use UK dates.?

 

[arnelgp]

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.

 

[Gasman]

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.

I am not convinced tbh.
I used your first set of code and got the value 4, which is correct.?

I am not convinced that the correct dates and times are being supplied.?
I cannot think what else it could be. I have shown my code and result from the immediate window, and that is with your original code.?

Edit:
Just tried your second bit of code which I called workhrs1 and I still get 4 for that last line.?

? 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

 

[Lilly420]

Hello, thank you again. I used your new code and I now get 4. I also created a new function call MyWorkHours that is supposed to give the hours between the two dates correct but it still takes into account the break or at least on the first example below?

Function:
Public Function myWorkHours(date1 As Date, date2 As Date) As Double
myWorkHours = WorkHrs(date1, date2)
End Function

Results:

? myWorkHours (#7/31/2019 2 pm#, #8/1/2019 1 pm#) 7 (3 hours, 5 Hours?)

? myWorkHours (#7/31/2019 12 pm#, #7/31/2019 4 pm#) 4

? myWorkHours (#7/29/2019 2 pm#, #7/30/2019 9 am#) 4

So sorry for this, I don't mean to take all your time...but appreciate you both so much.

Lilly

 

[arnelgp]

1. #7/31/2019 2 pm# ==>from 2pm to 5pm (3 hrs)
2.#8/1/2019 1 pm# ==> from 8am to 12noon (4 hrs), 1pm is still break time (12noon-1pm).

this is as per business hours you are after.

8am - 12pm (4 hrs)
12pm - 1pm (0 hrs) break
1pm-5pm (4hrs)

on real world the above is correct, since I am using Timecard all my life.

its your data, I don't know how to break it up.

if we don't take the break into account, youll be working straight time 8am-4pm (8hrs)?
then we will need to build new code.

 

[Lilly420]

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

 

[Gasman]

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

I've already told you, if you use the same time for lunch start and end, you will get no lunch deducted.?

So hard code that in your function for when you call arne's function?

 

[Lilly420]

Please forgive me, I did changed my query to "12pm", "12pm" like you suggested, and it works on some of the dates but not on all for instance:

SELECT WorkHrs([MPDateExtractStarted],[MPDateExtractFinished],"8 am","5 pm","12 pm","12 pm") AS WorkHours
FROM tblMonarchPerformance LEFT JOIN tblMonarchCustomer ON tblMonarchPerformance.MPCustID = tblMonarchCustomer.CustID;

Start 8/1/2019 9:00am, ending on 8/2/2019 1pm = 11 hours

Using the ("12pm", "12pm") as you suggested in my query...I would think 8 hours day 1 and 5 hours day 2 for a total of 13 without breaks, I get 11 which would be taking out the 1 hour on 8/1 and 1 hour on 8/2 for 11 or am I wrong?

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.

I also updated the code to read the following with the same result:

Public Function WorkHrs( _
dt1 As Date, _
dt2 As Date, _
Optional sWorkHrStart As String = "8 am", _
Optional sWorkHrEnd As String = "5 pm", _
Optional sBrkHrStart As String = "12 pm", _
Optional sBrkHrEnd As String = "12 pm") As Double
Const hr1 As Double = 4.16666666715173E-02
Const Min1 As Double = 6.94444444444442E-04

Lilly

 

[Lilly420]

Results:

? myWorkHours (#8/1/2019 9 am#, #8/2/2019 1 pm#) 11

 

[Gasman]

Yes, not working 100% for me now

 ? 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

I'm going to leave you in the capable hands of arne, as I just seem to be muddying the waters, as I really believed it was working correctly.

Sorry for the confusion, even though I get different results from you on the same data.

 ? 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

workhrs1 is arne's second code

 

[theDBguy]

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. 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:


8AM to 5PM = 9 hrs

8/1/2019 8AM to 8/1/2019 5PM = 9 hrs
8/2/2019 8AM to 8/2/2019 9AM = 1 hr


So, that's 9 hrs for the first day (8/1) and 1 hr for the second day (8/2), which mean 10 total hrs. So, why would you say 9 hrs is correct?


Just a little confused right now...

 

[Lilly420]

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

 

[Lilly420]

Dear Gasman,

Thanks for all your help, I appreciate it so much.

Lilly

 

[theDBguy]

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

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.