We help IT Professionals succeed at work.

Need to track time each day between 2 time periods then obtain sum of total hours in a week

mickeyshelley1
on
I am trying to create a simple to track total time spent on an activity each day so that I can have a weekly total of hours spent on that activity.

I have a form with 3 textboxes as follows:

txtStarttime  (Bound to Time/Date data type field in table)
txtEndtime  (Bound to Time/Date data type field in table)
txtTotaltime (Bound to Time/Date data type field in table; I tried using a number format so that I could calculate total hours but  I received a data type mismatch error)

I need to be able to retrieve all the total time values to obtain the sum value, my problem is it only works if I format the txtTotaltime table field as a textbox which will not calculate total sum value
Note: Both, txtStarttime and txtEndtime are use the short time format. Below is the code I am using…

Private Sub txtTimeStop_LostFocus()
If [txtTimeStop] > [txtTimeStart] Then
txtTotalHours.Value = Format([txtTimeStop] - [txtTimeStart], "hh:nn")
Else
   txtTotalHours.Value = Format([txtTimeStop] + 1 - [txtTimeStart], "hh:nn")
End If
End Sub

Open in new window

Comment
Watch Question

Top Expert 2016

Commented:
not sure what format you want to store the computed time,
see this link for reference

How to store, calculate, and compare Date/Time data in Microsoft Access
http://support.microsoft.com/default.aspx?kbid=210276

Author

Commented:
Hey Cap this is a community service database, each day a start time and a finish time is entered into each record and the total hrs are currently stored in a textbox with a text format field, the problem is that I am unable to total the hours with a query because of the format of that field like I could if it were a number format. When I use a number format I get an error related to data type mismatch..
Top Expert 2016
Commented:

try this, store the values in Data Type Number ,Field Size Double

Private Sub txtTimeStop_LostFocus()
If [txtTimeStop] > [txtTimeStart] Then
txtTotalHours.Value = ([txtTimeStop] - [txtTimeStart]) * 24
Else
   txtTotalHours.Value = ([txtTimeStop] + 1 - [txtTimeStart]) *24
End If
End Sub

Author

Commented:
You ALWAYS solve my problems…thank you so much AGAIN CAP

Commented:
if you have your times as strings in the format say "HH:MM:AM/PM" then you could use the following to just take the starting time as a string and the end time as a string and return the hours/min between...its just an example...

This very basic function assumes you are supplying an end time that is after the start time and that the format also contains AM PM....so try it this way

Debug.Print CompareTime("9:55AM","3:25PM")

Open in new window



Public Function CompareTime(tStart As String, tEnd As String) As Long
Dim sHour As String, sMin As String, eHour As String, eMin As String
Dim realStart As Date, realEnd As Date

tStart = Left(tStart, Len(tStart) - 2)
sHour = Mid(tStart, 1, InStr(1, tStart, ":") - 1)
sMin = Mid(tStart, InStr(1, tStart, ":") + 1, 2)
realStart = TimeSerial(Val(sHour), Val(sMin), 0)

If Right(tEnd, 2) = "PM" Then
    tEnd = Left(tEnd, Len(tEnd) - 2)
    eHour = (Mid(tEnd, 1, InStr(1, tEnd, ":") - 1)) + 12
Else
    tEnd = Left(tEnd, Len(tEnd) - 2)
    eHour = Mid(tEnd, 1, InStr(1, tEnd, ":") - 1)
End If
eMin = Mid(tEnd, InStr(1, tEnd, ":") + 1, 2)
realEnd = TimeSerial(Val(eHour), Val(eMin), 0)

CompareTime = DateDiff("n", realStart, realEnd) / 60

End Function

Open in new window