mickeyshelley1
asked on
Format field to accept total time data HH:nn
I have a form that tracks total time spent on a project in hours and min. It contains the start time, Completed time and Total time fields. The problem is that I the total time field will only store the value if it is a text field and on the report I need to calculate the sum of all the totaltimes column in the report footer. I cant get it to calculate the text data format.
Starttime (dateTimeformat)
CompleteTime (DateTimeFormat)
TotalTime (number Format)
Starttime (dateTimeformat)
CompleteTime (DateTimeFormat)
TotalTime (number Format)
Private Sub Command6_Click()
If [Completetime] > [starttime] Then
TotalTime.Value = Format([Completetime] - [starttime], "hh:nn")
Else
TotalTime.Value = Format([Completedtime] + 1 - [starttime], "hh:nn")
End If
End Sub
ASKER
I entered a start time of 08:00 and a completed time of 14:00 and the Totaltime value was 0.25 rather than 6:00
that is correct 0.25 is 1/4 of 1 day (24 hrs )
if you do this
format( 0.25, "h:nn")
it will give you 6:00
if you do this
format( 0.25, "h:nn")
it will give you 6:00
ASKER
If i leave it as is will it allow me to calculate the total hours and min of all the entries in the total time column in a report?
do you want to display hh:nn in the report per record ?
do you want a total value in the format hh:nn too ?
create a query with one column for display and another column for calculations.
do you want a total value in the format hh:nn too ?
create a query with one column for display and another column for calculations.
It is very complicated to accurately do math on dates and times stored as text such as "8:30" or "2:15 PM" (not impossible, but when I did this for an Outlook custom form time sheet, the code for converting the text In and Out times for three work shifts into numbers and doing the calculations for various subtotals and totals ran over 50 pages). I would recommend storing all the date/time values in Date fields, and doing the calculations using these fields, so you can use DateAdd and DateDiff to do the match. Then you can use the Format function to convert them into the desired formats for display.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
(do you want to display hh:nn in the report per record ?)
The priority is for the form to capture the total hours/min on each day, in the proper format so that I can display the total time value in hours and mins on the footer of a report. Kinda like a time clock would capture the total hours worked in a pay period
The priority is for the form to capture the total hours/min on each day, in the proper format so that I can display the total time value in hours and mins on the footer of a report. Kinda like a time clock would capture the total hours worked in a pay period
Always store date and time using data type Date.
Then, for display, apply the format you wish.
Thus:
When you sum these, the total will probably exceed 24 hours.
Use this function to convert such values to a string of hh:nn
=FormatHourMinute(Sum([Tot alTime]))
/gustav
Then, for display, apply the format you wish.
Thus:
Private Sub Command6_Click()
If [Completetime] > [starttime] Then
TotalTime.Value = [Completedtime] - [starttime]
Else
TotalTime.Value = [Completedtime] - [starttime] + 1
End If
End Sub
Then apply the format h:nn to TotalTime.When you sum these, the total will probably exceed 24 hours.
Use this function to convert such values to a string of hh:nn
Public Function FormatHourMinute( _
ByVal datTime As Date, _
Optional ByVal strSeparator As String = ":") _
As String
' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
' datTime: #10:03# + #20:01#
' returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.
Dim strHour As String
Dim strMinute As String
Dim strHourMinute As String
strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
' Add leading zero to minute count when needed.
strMinute = Right("0" & CStr(Minute(datTime)), 2)
strHourMinute = strHour & strSeparator & strMinute
FormatHourMinute = strHourMinute
End Function
For example:=FormatHourMinute(Sum([Tot
/gustav
ASKER
Thank you I will try it..
ASKER
I just downloaded it thanks Cap
ASKER
Thanks Cap, that is what i needed...
with this type, you can easily make calculations
you can display it in hh:nn with
format([TotalTime],"hh:nn"
use this codes
Private Sub Command6_Click()
If [Completetime] > [starttime] Then
TotalTime.Value = [Completetime] - [starttime]
Else
TotalTime.Value = [Completedtime] + 1 - [starttime]
End If
End Sub