24hr clock problem

[elsanto]

Hey all,

I'm setting up a db to calculate turnaround times - time in vs time out. Both fields contain date and time inclusive, time being 24hr clock.

I'm running a basic query that subtracts the Received field from the Sent field, and the result is formatted to hh:mm, 24hr clock.

The problem is that I can't get the time to accrue like it would in Excel using [hh]:mm. After it reaches 23:59, it resets back to 00:00. This means that if a turnaround time is greater than a day, as they often are, the results are going to be incorrect.

Is there a way to get around this as in Excel? Or am I going to need to use another query?

Any suggestions would be greatly appreciated.
Many thanks,

tj.

 

[ColinEssex]

This question occurs on a regular basis. Have a search, I know there's code been posted to address this.

Post back if no luck

Col

 

[elsanto]

Hey Col,

Thanks for your reply. I didn't expect anything so quick!
Ran a search but couldn't find what I was after.
Any suggestoins?
Cheers.

tj.

 

[ColinEssex]

Check out DateDiff in Access help

Col

 

[elsanto]

Col,
Thanks again.
My function now looks like this:

TA Team: DateDiff("n",[Received],[Sent])

...where [Sent] and [Received] are date/time format (dd/mm/yy hh:nn)

I still can't get it to return the time in format hh:nn.
Any suggestions?
Cheers.

 

[raskew]

Your DateDiff() function is not returning a time, but rather an integer representing number of minutes, e.g. 662.

To format that result try (debug window example):

numMins = 662
? numMins\60 & ":" & Format(numMins Mod 60, "00")
11:02

 

[neileg]

?? Where's the hard bit??

Select your TA Team field in the QBE grid and show Properties. In Format, type hh:mm.

The function works well for me just subtracting the dates without DateDiff, i.e. TA Team: [Received]-[Sent]

 

[elsanto]

Neil,

Thanks for your reply.

I'm not sure if you fully understand the problem.
I need to calculate the difference between the Received and Sent fields. For example,

Received: 25/5/3 13.15
Sent: 27/5/3 12.45
Actual Difference: 47.30
Access Calculates Difference as: 23.30

The problem I'm encoutering is that Access doesn't accrue the time difference over 23.59hrs. It resets to 00.00. Access would read the above calculation as 23.30, disregarding the additional 24hrs that should be part of the actual difference.

I think I explained all this in the original question.
Regards,

 

[neileg]

Mumbles....RTFQ, idiot

Sorry about that. Actually, you are only partly right. Access does not get the calculation wrong, it's simply that applying the hrs and mins format excludes the days portion of the answer, so apparently giving you the wrong answer. I don't think there's a way you can get Access to display the total hours simply by changing the format.

What you can do is build the display value you want. Assuming you already have the TA Team value as the difference between the two times expressed as a decimal, i.e. 1.979166666 in your example, then a further calculated field like this will do the display
HrsMins: Int([TA Team]*24) & ":" & Round(((([TA Team]*24)-Int([TA Team]*24))*60),0)

You'll have to keep the decimal value if you want to total this or use it in any calculations, and then convert the result in the same way as above.

Hope this is a better answer than my last one!

This does not take away from raskews answer which achieves a similar effect based on the DateDiff function. Depends which you prefer, really.

 

[bbeeching]

I had the same problem not too long ago. I posted a simular question on the UtterAccess Forum and recieved this reply from R. Hicks, (quote)

"When using the DateDiff() function to calculate "elapsed" time between two time values and the ending time value spans past midnight ...... You will receive a negative result from the DateDiff() function ..........

Here is an explanation as to why and a possible alternative to get the result needed.

The problem is cause by the data entry being only a Time value.

A Date/Time datatype "must contain a Date value".

So if you only enter a Time value ... Access will assume the Date to be the current Date when the entry is made. So here is the problem created by this "assumption" when the ending value spans past midnight into another day.

You enter 11:00 PM as the start value and 1:45 AM as the ending value .... Access will store both time values and assume that the Times are on the current date.

So here is how Access evaluates the entries and what ends up being stored:

11/03/02 11:00 PM .. as the Start value
11/03/02 1:00 AM .. as the Ending value
(if date entered is... 11/03/02)

So if you use the two values above in your DateDiff() ... you get a negative number ........

The correct solution to the problem is to enter the "Date and Time" as the entries and the problem will not show it's ugly head.

But .... if the two Time entries will "never span more than a 24 hour period" ..... you can evaluate the entries then manipulate them to give the needed result.

Here is a Function that will handle this problem.


code:--------------------------------------------------------------------------------Public Function fElapsedTime(vStartTime As Variant, vEndTime As Variant) As String
Dim intHrs As Integer, intMins As Integer
If Not IsDate(vStartTime) Or Not IsDate(vEndTime) Then
Exit Function
If vStartTime > vEndTime Then intMins = DateDiff("n", DateAdd("d", -1, vStartTime), vEndTime)
Else
intMins = DateDiff("n", vStartTime, vEndTime)
End If
intHrs = intMins \ 60intMins = intMins Mod 60 fElapsedTime = intHrs & ":" & Format(intMins, "00")
End Function
--------------------------------------------------------------------------------


Ricky Hicks

Thanks to Ricky, my time calculations work perfectly! I hope this helps you ... It did for me!

 

[raskew]

Think the bottom line is that the format() function just won't take a number of minutes and return a hh:nn string. You have to write it yourself. From the debug window:

x = #May-25-2003 13:15#
y = #May-27-2003 12:45#
z= datediff("n", x, y)
? z
2850
? z\60 & ":" & Format(z Mod 60, "00")
47:30

Perhaps the problem is in thinking that that the datediff() function returns a date or time. It doesn't, it returns an integer (number of minutes). That is not a time, as in 2:15 PM. It's elapsed time, a period of time expressed in hours, minutes, seconds or however you choose to set it up.

 

[elsanto]

Many thanks all for your help. I didn't expect so many responses so fast! I have yet to read through and work out which one works best for my particular problem. Shall do so today.

I love the forum! More resources here than I could have hoped for. I'll be back!
Cheers.

 

[bbeeching]

I forgot to say that I use the function from a query...

ElapsedTime: fElapsedTime([StopTime]-[StartTime])

Cheers!