Total time subtract 30

[teel73]

Hello, I'm hoping someone can assist me with this issue as I have been working for 5 days on this. I am trying to calculate the total time for 1 day between 2 times and subtract 30 minutes for the break. Then I need the sum of the time for the week. for Example: Johnny worked 2 days, on day 1 he clocked in at 07:45 and clocked out at 16:30. Then on day 2 he clocked in at 08:00 and clocked out at 16:45. I need to get the total time he worked on day 1 and day 2 subtracting 30 minutes. Then I need the total time he worked for the 2 days together.

I found the following function that works fine to give me the total time but it doesn't subtract the 30 minutes. I've tried several modifications to the code but can't seem to get the correct results. Any help would be much appreciated. Here's the function:

Public Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function
hours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60
totalseconds = Int(CSng(interval * 86400))  ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60
If seconds > 30 Then minutes = minutes + 1  ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours
HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function

I basically call this function in the control on a form. Example: =HoursAndMinutes([TimeOut]-[TimeIn])

Then for the sum of the that: =HoursAndMinutes(Sum([TimeOut]-[TimeIn]))

If only I can get the code to subtract the 30 minutes for lunch, I'd be oh so grateful.

Thanks

 

[Brianwarnock]

do it in the call as the function will be difficult to change, or at least messy
try
Example: =HoursAndMinutes([TimeOut]-[TimeIn]-#00:30:00#)

 

[teel73]

Thanks but that didn't work .. when I put in the -#00:30# .. it converted the expression to : =HoursAndMinutes([TimeOut]-[TimeIn])-#12:30:00 AM# which resulted in #Error

 

[Brianwarnock]

Yeah time is a pain at times. I do not have access to Access at the mo to experiment but to do it in the function you would need to take 30 from the minutes if this becomes minus subtract the answer from 60 to get the actual minutes and 1 from the hour to correct that.

Brian

 

[teel73]

I don't understand and I don't wanna quit but I can't find any help on this. I even tried google.

 

[Brianwarnock]

Ok once the function has calculated the minutes it something like this

Minutes = minutes-30
If minutes<0 then
Minutes = 60 -minutes
Hours= hours-1
End if

Brian