Get time elapsed without calculations

[Rose412]

I don't know if this is possible.

I have four fields [StartDate] [StartTime] [EndDate] [EndTime] along with other fields imported from Excel into an Access 2K table.

In a query, I need to show the time elapsed between Start and End for each record in the format: ? day ? hr ? min ? sec

I know I need to write a function in a module but I am not good at mathematics. Since the four fields are all date/time fields, is it possible to write the function using only the built-in date/time functions?

Thanks in advance.

Rose

 

[raskew]

Rose -

Try searching on 'elapsed'. Yours is a frequent topic and there are a number of potential solutions already published.

HTH-

Bob

 

[Rose412]

Thanks for your response. I had searched before I posted, but the posts that turned up were either about other subjects or using calculations that were beyond me. For instance, one of the threads contained a link to this function in a Microsoft KB article.

Function GetElapsedTime (interval)
Dim totalhours As Long, totalminutes As Long, totalseconds As _
Long
Dim days As Long, hours As Long, Minutes As Long, Seconds As Long

days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
Seconds = totalseconds Mod 60

GetElapsedTime = days & " Days " & hours & " Hours " & Minutes & _
" Minutes " & Seconds & " Seconds "
End Function


I can use this function in my query without having to know what its code really does. But if it is possible, I want to have a function whose code I can understand.

Hopefully someone knows how to do it with only the date/time functions and get rid of the calculations.

 

[RV]

You'll have to use a user-defined function (VBA code).
You can't solve your problem using built in functions in Access.

Good news is, you don't need any knowledge of mathematics, just logical sense

RV

 

[raskew]

Rose-

As RV pointed out, you’re not going to be able to complete your task using strictly built-in date/time functions.

Think it'll be helpful if you read-up on how Access stores dates and times in date/time data format. For this, check-out the following MSKB site:
support.microsoft.com/support/kb/articles/Q130/5/14.ASP

There are a couple of ways you might attack the situation.
1) The MSKB code you posted (and found confusing) is intended to work with a situation where you, for example, subtract one date/time from another, as in (all of this from the debug window):

myNewDT = now()
? myNewDT
11/22/03 7:00:36 PM 

myOldDT = #11/20/03 5:15:23 AM#
? myOldDT
11/20/03 5:15:23 AM 

interval = myNewDT - myOldDT
? interval
 2.57306712963327

Interval is telling you that the difference between the two date/times is a tad over 2-1/2 days.

If you run Function GetElapsedTime( 2.57306712963327), it'll spit-out the days, hours, minutes, seconds equivalent. Step through the code line-by-line and look at the values created.

I'm going to save the second half of the answer until you post back to say that you've experimented with the above and have at least a marginal understanding of what's happening.

HTH and please post back-

Bob

 

[Rose412]

Thanks RV and Bob.

After playing around in the debug window and looking at figures like the following:-

      myNewDT = now()
      ? myNewDT
      11/23/2003 11:33:43 AM

      myOldDT = #11/20/03 5:15:23 AM#
      ? myolddt
      11/20/2003 5:15:23 AM

      interval = myNewDT - myOldDT
      ? interval
       3.26273148148175

      ? getelapsedtime(interval)
      3 Days 6 Hours 18 Minutes 20 Seconds


I begin to see that when we subtract one date/time variable from another, the difference we get is a number with some long decimal places. The number of days between the variables is represented by the integer in the difference, while the time between the variables is represented by the decimals. So my solution would be simply to extract the hours, minutes and seconds too from the decimals in the difference.

And that is exactly what the confusing MSKB code does.

Am I in the right direction?

 

[raskew]

Great! You're right on target.

A second (and probably more frequently used) method involves the datediff() function.
Since you want seconds to be returned as part of the answer, specify "s" as the desired interval.
Using your previous example:

myNewDT = #11/23/03 11:33:43 AM# 
myOldDT = #11/20/03 5:15:23 AM#

x = datediff("s", myOldDt, myNewDt)

? x
 281900 

This is the total number of seconds between the two dates.  
You'll need a user-defined function to convert # of seconds to  DD:HH:MM:SS format.  
Here's one you can try:

Function TimeSpentS(ByVal pSec As Single) As String
'*******************************************
'Name:      TimeSpentS (Function)
'Purpose:   Formats an integer (number of
'           seconds) into a string
'Inputs:    From debug window:
'? TimeSpentS(281900)
'Output:    03:06:18:20
'*******************************************
'
Dim days, hours, minutes, secs, timehold As Single
Dim fmt As String

fmt = "00:"

timehold = pSec

days = Int(timehold / 86400)

timehold = timehold - (days * 86400)

hours = Int(timehold / 3600)

timehold = timehold - (hours * 3600)

minutes = Int(timehold / 60)

seconds = timehold Mod 60
'
TimeSpentS = Format(LTrim(Str(days)), fmt) & Format(LTrim(Str(hours)), fmt) _
& Format(LTrim(Str(minutes)), fmt) & Format(LTrim(Str(seconds)), "00")

End Function

 

[Jon K]

Here's a function that doesn't require calculations.

----------------------
Public Function MyGetElapsedTime(interval) As String

Dim Day As String
Dim Time As String

Day = Int(interval) & " day "
Time = Format(interval, "h \h\r n \m\i\n s \s\e\c")

MyGetElapsedTime = Day & Time

End Function
----------------------

The individual h n s return the numbers of hours, minutes and seconds. A backslash \ tells Access to display the next character as a literal character.

Since your data are contained in four fields, you can pass them to the function with:-

MyGetElapsedTime(([EndDate]+[EndTime]) - ([StartDate]+[StartTime]))

Note For the function to work, the fields must not contain Null values.

 

[raskew]

Jon K -

I've gone brain-dead trying to test your solution. Would you please post an example using:

myNewDT = #11/23/03 11:33:43 AM#
myOldDT = #11/20/03 5:15:23 AM#

Thanks and best wishes-

Bob

p.s. The poster wants to see days/ hours/ minutes/ seconds.

 

[Jon K]

Bob,

I have attached a database, which contains my function and the MSKB function. For 11/20/03 5:15:23 AM to 11/23/03 11:33:43 AM, both functions return the same result:-
My function:     3 day 6 hr 18 min 20 sec
MSKB function: 3 Days 6 Hours 18 Minutes 20 Seconds

From the following debug-window results, we can see that Interval is actually a date/time variable.

myNewDT = #11/23/03 11:33:43 AM#
myOldDT = #11/20/03 5:15:23 AM# 
Interval=myNewDT-MyOldDT

? Format(interval,"General Date")
1/2/1900 6:18:20 AM

? MyGetElapsedTime(interval)
3 day 6 hr 18 min 20 sec

Hence the numbers of days, hours, minutes and seconds are already there. My function simply uses the Int() function and the Format() function to extract them from it.

Jon K

 

[Rose412]

Thanks everyone for your input.

Now I have a much deeper understanding of using date time fields and functions.

Bob, after changing secs to seconds in the Dim statement, your function gives me mostly zeroes. For example, when I used it on the 7 records in Jon K database with:-
TimeSpents: TimeSpents(([EndDate]+[EndTime])-([StartDate]+[StartTime]))

I got:-
TimeSpents
00:00:00:01
00:00:03:04
00:00:03:03
00:00:00:00
00:00:00:01
00:00:00:00
00:00:00:03


Jon K, your function works. I find it the simplest and the easiest to understand.

Thanks again.

Rose

 

[Jon K]

Rose,

Bob's function works. But, instead of requiring you to pass to it the "Interval" i.e. the difference of ([EndDate]+[EndTime]) - ([StartDate]+[StartTime]), it requires you to pass to it the total time elapsed in seconds like this:-
TimeSpentS: TimeSpents(DateDiff("s", ([StartDate]+[StartTime]), ([EndDate]+[EndTime])))

When used on the seven records in my database, it should return the days, hours, minutes and seconds like these:-

TimeSpentS
01:01:03:01
183:12:15:00
183:00:00:00
00:00:45:38
00:13:55:48
00:10:59:00
03:06:18:20

 

[raskew]

Thanks Jon for providing that explanation.

Rose - You'd most likely use that method in conjunction with the DateDiff() function where you're returning number of seconds as the interval.

Jon - Guess because I've always used DateDiff() versus subtracting one date/time from another, it just never dawned that your example method in fact returns a double which could then be formatted.

Have to admit that upon seeing your solution, I sat and stared at that
"Format(interval, "h \h\r n \m\i\n s \s\e\c")" line, wondering why all the backslashes. Finally, realized they were denoting literals, just like it shows in the help file. Duh! To quote someone, 'I may not be good but I sure am slow!'.

Think that, provided you can return a pseudo-date in date/time data format, it's a superior solution since the format function does the work for you.

Thanks for providing it.

Best wishes,

Bob