Hour Calculation

[Sevi]

Hi,

In a table I have two fields (Date/Time) with data entered. The format of
data entered is as follows:

Field 1 Filed 2
25/02/09 08:10 PM 25/02/09 08:28 PM

How to get the third field (Number) Auto filled with total time taken (HH.MM)
Can any one please help.

Smiles Sevi

 

[Douglas J. Steele]

You should not be storing Field3 in the table: calculated values seldom (if
ever) should be stored.

Instead, only have Field1 and Field2 in your table, then create a query that
includes Field3 as a calculation. You can then use the query wherever you
would otherwise have used the table.

See http://www.accessmvp.com/DJSteele/Diff2Dates.html for one function that
will do the calculation for you.

 

[ken]

Do not store the time taken in a separate column unless you want to be
able to change it independently of the two times. Storing values
which can be computed from other values introduces redundancy and the
possibility of inconsistent data. If it will always be the difference
between the two times then return it in a query in computed column in
a computed control in form or report. If you are willing to rely on
Access's implementation of date/time values the following function
will return the difference in hours and minutes between two date/time
values and also gives you the option of including days in the return
value where the difference is 24 hours or more, returning it as either
something like 1:2:35 or 26:35. The latter is the default. Add the
function to a standard module in your database:


Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutes As String
Dim strDaysHours As String
Dim dblDuration As Double

dblDuration = dtmTo - dtmFrom

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutes = Format(dblDuration, ":nn")

If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY

TimeDuration = strDaysHours & strMinutes
Else
TimeDuration = lngHours & strMinutes
End If

End Function


If it’s a new module then save the module under a different name, e.g.
mdlDateTimeStuff

In a computed control on a form for instance you'd then set its
ControlSource to:

=TimeDuration([Field1],[Field2])

in a query you'd enter the following in the 'field' row of a blank
column in the design grid:

TimeTaken:TimeDuration([Field1],[Field2])

If you prefer not to rely on the implementation the following
expression can be used:

DateDiff("h",[Field1],[Field2]) & ":" & Format(DateDiff("n",[Field1],
[Field2]) Mod 60,"00")

or to include days:

DateDiff("d",[Field1],[Field2]) & ":" & Format(DateDiff("h",[Field1],
[Field2]) Mod 24,"00") & ":" & Format(DateDiff("n",[Field1],[Field2])
Mod 60,"00")

Ken Sheridan
Stafford, England