Calculating total number of hours worked

[hooby1]

I have a form that shows the start time and finish time (timesheet) as well as other information. The question is, how do you make a textbox calculate the total number of hours worked? I have manually entered the number of hours worked, but just need the report to show the grand total of the week's hours. Have tried some calculations with textboxes but some work and some don't (one showed a negative number, but this was due to the hours worked being 6pm 12am, so I'm guessing you need to code it to say if it runs into the next day.
Are there any properties of the fields that need to be correct in order for this to work? I have made the start and finish times time/date types and because I have entered the worked hours out manually, this field type is a text.

Any Ideas?

 

[andy_25]

I remember having problems with this in Excel.

Will subtracting the times not work? I.e. Control Source of the textbox =[Start Time]-[End Time]

 

[andy_25]

Be sure to format the textbox as short time or it will show a strange working out of the subtraction.

 

[hooby1]

Andy_25,
I have the week's hours in but I would then like a grand total of the hours worked for that week. Your way only calculated the last inputted entry.

 

[andy_25]

You will need to put a Sum textbox in the report footer with the controlsource =([HoursWorked])

[HoursWorked] being the control name where your total hours for the day/shift are stored.

 

[SOS]

You will need to put a Sum textbox in the report footer with the controlsource =([HoursWorked])

[HoursWorked] being the control name where your total hours for the day/shift are stored.

If you do this you should rename your CONTROL to txtHoursWorked and then use the FIELD name of [HoursWorked]. Aggregate functions on reports do not like using the controls if the field is available AND it will usually give you a #Name error if you have the control and the field named the same.

 

[andy_25]

If you do this you should rename your CONTROL to txtHoursWorked and then use the FIELD name of [HoursWorked]. Aggregate functions on reports do not like using the controls if the field is available AND it will usually give you a #Name error if you have the control and the field named the same.

Very true SOS, should have mentioned that. So in summary:

Change the name of your textbox with the total hours worked for the day so it is not the same name as the control. I.e. change HoursWorked to txtHoursWorked as mentioned.

Add a textbox and change the controlsource to =sum([txtHoursWorked]) to sum up all the instances of that control on the report.

 

[hooby1]

Many thanks for the help. After making changes and a few modifications it now works.
Many thanks.

 

[DCrake]

When working with times and calculating the difference and using the difference for reportng purposes I find it better not only to store the times as normal but also to store it as number of minutes past midnight.

03:00 becomes 180
10:30 becomes 630

Duration : 630-180 = 440

Then you sum the duration field to give you the total minutes and have a little function to convert minutes into time format. Don't forget if you summary field is set as date/time it can't handle times over 24 hours.

 

[Dazzy]

I am having a similar problem with our database, the total hours on form for a given week is calculated in the query using this

TotHoursAttended: HoursAndMinutes(([MTimeout]-[Mtimein])+([TTimeout]-[Ttimein])+([WTimeout]-[Wtimein])+([ThTimeout]-[Thtimein])+([FTimeout]-[Ftimein]))

But when I got to total all hours attended on the report I get an error saying it is to complex or something like that.

Have you an example of converting to real numbers DClarke?

Thanks

 

[Brianwarnock]

When working with time calculations you have to work with a base unit and do conversions, if the time can run over 24 hours you have to have a Date in there then a simple function can calculate the time.
the one below does not answer all of the questions here but may help point people in the right direction.

Function totaltime(date1 As Date, date2 As Date) As String

'BJW
' This calculates times allowing for greater than 24 hours

Dim inthours As Long
Dim intmins As Integer
Dim intsecs As Integer
Dim totalsecs As String

totalsecs = DateDiff("S", date1, date2)
inthours = Int(totalsecs / 3600)
intmins = Int((totalsecs - inthours * 3600) / 60)
intsecs = Int(totalsecs - (inthours * 3600 + intmins * 60))
strhours = Format(inthours, "00")
strmins = Format(intmins, "00")
strsecs = Format(intsecs, "00")

totaltime2 = (strhours & ":" & strmins & ":" & strsecs)

End Function

 

[Dazzy]

Thanks

I am already using this, which I found on the forum.

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))
  
  ' 1440 = 24 hrs * 60 mins
  totalminutes = Int(CSng(interval * 1440))
  minutes = totalminutes Mod 60
  
  ' 86400 = 1440 * 60 secs
  totalseconds = Int(CSng(interval * 86400))
  seconds = totalseconds Mod 60

  ' Round up the minutes and adjust hours
  If seconds > 30 Then minutes = minutes + 1
  If minutes > 59 Then hours = hours + 1: minutes = 0
  HoursAndMinutes = hours & ":" & Format(minutes, "00")

End Function

I think I found a work around now, I will display the exact time for each week on the report, have the query calculate another field, hide this on the report and sum it up, will be rounded to nearest hour but it's close enough for this purpose.