CALCULATING BREAKS/LUNCHES, ETC. 1

[ado2002]

Hi

I have a Production Database. It takes into consideration Start Time, Lunch, End Time and Break 1, Break 2 and Break 3.

I would like to create a formula to give me the total hours worked for this individual minus his breaks and lunches.

I have yes/no boxes for breaks/lunches which say
=IIf([break1]=Yes,".25","0"), etc.

My problem is that I need a formula to calculate all the fields.

I have this also ([End time]-[Start time])*24

Does anybody have any suggestions????

Thank you,
Anjie

 

[JoanneM]

ok Anjie here goes nothing

This comes from a complex work hours calculation that I have reduce to bare bones for your requirement

You will need a form or some method to supply the query with an id for your staff member

copy into your query - hourstot in one column and minswrkd in another

hourstot: hrswrkd([start_date],[start_time],[end_date],[end_time],"h", [TblStaffMember]![intstaffid])

minswrkd: hrswrkd([start_date],[start_time],[end_date],[end_time],"m", [TblStaffMember]![intstaffid])

replace the blue text with your own variable for staff id

NB you are calling the same function I know but note the "h" and "m"
this becomes Retvalue in the function meaning returned value
h= hours
m = mins

b1:=IIf([break1]=Yes,"15","0")
b2:=IIf([break2]=Yes,"15","0")
LB:IIf([lunchbreak]=Yes,"60","0")
totMinsBrk:sum(cint(b1)+ cint(b2)+ cint(lunchbreak))
totminswrkd: hourstot*60 + minswrkd - totMinsBrk
PaidHrs: totminswrkd /60
paidmins:totminswrkd mod 60


Error trapping has been removed to maintain uniformity in your code ( so you can use your own in short)
next create a new module BasCaclHrs

copy this vba into it
after the option explicit

Public Type TimeParts
Dy As Integer
Hr As Integer
mins As Integer
Secnds As Integer
End Type
you need to copy the following functions
[ul]
[li]hrsWrkd [/li]
[li]GetElapsedTime[/li]
[li]cvstrTodate[/li]
[/ul]

Function hrsWrkd(S_date, s_time, e_date, e_time, RetValue As String, id) As Integer
'Exit Function
If IsNull(S_date) Then hrsWrkd = 0: Exit Function
If IsNull(e_date) Then hrsWrkd = 0: Exit Function
If IsNull(s_time) Then hrsWrkd = 0: Exit Function
If IsNull(e_time) Then hrsWrkd = 0: Exit Function 'these records have their values set to zero so they can be identified as records requiring further input.
Dim ENDTIME As Date
Dim Startime As Date
Dim startStr As String
Dim EndStr As String
Dim DyHours As Integer
Dim Hrs As Integer
Dim minits As Integer
Dim secnd As Integer
Dim returnedhrs As TimeParts

'as the times are delivered to the function as strings - they have to be converted to  times
s_time = cvstrTodate(s_time)
e_time = cvstrTodate(e_time)

startStr = CStr(S_date & " " & s_time)
EndStr = CStr(e_date & " " & e_time)


Startime = Format(CVDate(startStr), "dd/mm/yyyy hh:nn:ss")
ENDTIME = Format(CVDate(EndStr), "dd/mm/yyyy hh:nn:ss")

returnedhrs = GetElapsedTime(ENDTIME - Startime)
''debug.Print id
DyHours = returnedhrs.Dy * 24
Hrs = returnedhrs.Hr

If RetValue = "h" Then hrsWrkd = DyHours + Hrs
If RetValue = "m" Then hrsWrkd = returnedhrs.mins

End Function

Function GetElapsedTime(interval) As TimeParts
         Dim Totalhours As Long, totalminutes As Long, totalseconds As Long
         Dim days As Long, hours As Long, Minutes As Long, Seconds As Long

         days = Int(CSng(interval))
         Totalhours = Int(CSng(interval * 24))
         totalminutes = Int(CSng(interval * 1440))
         totalseconds = Int(CSng(interval * 86400))
         hours = Totalhours Mod 24
         Minutes = totalminutes Mod 60
         Seconds = totalseconds Mod 60

    
     ' GetElapsedTime = days & " Days " & hours & " Hours " & Minutes & " Minutes " & Seconds & " Seconds "
      GetElapsedTime.Dy = days
      GetElapsedTime.Hr = hours
      GetElapsedTime.mins = Minutes
      GetElapsedTime.Secnds = Seconds
      
      End Function

& finally

Function cvstrTodate(TM)
Dim stime As Date
stime = Format(Left(TM, 2) & ":" & Right(TM, 2), "short time")

cvstrTodate = stime
End Function

You now have hours and mins calculated in your query
hope this helps...
happy easter
jo

 

[ado2002]

Wow!!!!

Great, thank you! This decreases my time tremendously!

Happy Easter
Anjie