Calculating Time..Have I seen this before? 3

[DBSSP]

I'm trying to get time to calculate itself based on the "24-hour" time. However I'm not sure how to get it to work. I keep getting gibberish numbers.

Example:
8am-4pm = 8hours

What I get is .111166334465 or somethig of the like. Any ideas on how to get this to calculate?

Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[CosmoKramer]

Here's a function I wrote to return a difference in hours between two date/time fields:

Public Function TimeElapsed(dIn As Variant, dOut As Variant) As Double
    If dOut - dIn > 0 Then
        TimeElapsed = ((CInt(Int(Format(dOut - dIn, "General Number"))) * 24) + DatePart("h", dOut - dIn)) + (DatePart("n", dOut - dIn) / 60)
    Else
        TimeElapsed = ""
    End If
End Function

Copy and paste that into a module. In your query, reference this function as a expression, passing it the start date/time and end date/time:

ElapsedTime: TimeElapsed([tblTime]![timein],[tblTime]![timeout])

 

[TonyJollans]

Hi DBSSP,

I'm sure you will have seen this before if you've been around this site for a while and, as always, it really depends on exactly what you want but in the simple case you quote you can use

DateDiff("h", "8am", "4pm&quot

If you want more complex formats, take a look at the Custom Formats, for example:

Format(TimeValue("4pm&quot - TimeValue("8am&quot, "hh:mm&quot

Enjoy,
Tony

 

[beetee]

Access dates are stored as floating point numbers. The whole part is the number of days since a starting point the Access developers chose (you can see that point by opening the debug window and typing Print CDate(0)).

The fractional part is the time within the day.

Since 1 day = 1, 1 hour = 1 / 24.

When you subtract 1 date from another, multiply the result by 24 to get the number of hours.

By again stripping off the 'whole' part, multipy the remainder by 60 and that's the number of minutes.

Here's some sample code:


Function FormatHHMM(ByVal InputValue As Double) As String
InputValue = InputValue * 24 ' convert to hours
Dim TheHours As Long
TheHours = Fix(InputValue + 1 / 120) ' 1/120 is 1/2 of a minute; and it removes a rounding error that
' can cause 60 minutes to be displayed instead of 1 hr

InputValue = InputValue - TheHours
Dim TheMinutes As Long
TheMinutes = InputValue * 60

Dim RetStr As String
If TheHours > 0 Then
RetStr = TheHours & &quot; hr&quot; & IIf(TheHours <> 1, &quot;s&quot;, &quot;&quot
End If

If TheMinutes > 0 Then
If Len(RetStr) > 0 Then RetStr = RetStr & &quot; &quot;
RetStr = RetStr & TheMinutes & &quot; min&quot; & IIf(TheMinutes <> 1, &quot;s&quot;, &quot;&quot
End If

FormatHHMM = RetStr
End Function

 

[DBSSP]

I've tried this, but it doesn't give me an accurate number.

DateDiff(&quot;h&quot;, &quot;8am&quot;, &quot;4pm&quot

It will tell me 2 hours, when it's only been 1 hour and 13 minutes. I've tried a different format, but I just get an error in the text box on the form...This seems to be the right track, though. Any ideas? I'll keep trying some different things, and will post back if I get it corrected before-hand. Thanks for the help so far though!

Jay
&quot;If the words up and down were reversed, would you trip and fall or trip and fly?&quot;

 

[TonyJollans]

Hi DBSSP,

Datediff provides limited capacity for finding differences in times. The first parameter (in this case “h”, for (whole) hours) tells it what units to return the value in. If you want minutes, use “n” instead. If you want it in hours and minutes you have to convert it yourself, either with your own code or by using a Custom Format.

As explained by beetee, time is held internally as a fractional part of a day. Custom formatting as a time requires input which can be understood as a time. If you have minutes, divide it by the number of minutes in a day, etc. Put it all together to show, for example, 73 minutes as “1:13” you could use

Format(DateDiff(&quot;n&quot;, &quot;8am&quot;, &quot;9:13am&quot / 1440, &quot;h:mm&quot

Enjoy,
Tony

 

[beetee]

Just FYI, Tony's example works fine when the number of hours is less than 24, the code example works for hours greater than 24.

 

[TonyJollans]

You are correct, beetee, but if we are finding the difference between two times it cannot be greater than 24 hours. If days / dates are added to the source values, the format codes can be amended to allow for them as well.

Enjoy,
Tony

 

[beetee]

You, too, have achieved accuracy in reiterating the original intent of the user. The attention of the preceeding message was to point out the difference between the two algorithms for others who may stumble across these solutions.

Certain applications (e.g. finding how many hours have been spent on a task) may want to format the data in hh:mm format (e.g. 402 hours, 32 minutes) rather than including days.

 

[DBSSP]

I finally figured it out! I had forgotten that each time element (hh, mm, ss) is recorded as a seperate entity my MS Office. With that in mind I just used the following equation to get the figure I was after.

((((Hour([Time Out])-Hour([Time In])))*60)+(Minute([Time Out])-Minute([Time In])))/60

This will give the decimal equivilant of the total hours.

But stars to those that helped! Thanks much.

Jay
&quot;If the words up and down were reversed, would you trip and fall or trip and fly?&quot;