Date difference module problem

[hjg]

I have a module which calculates the difference in days and hours between two date/times. That is, I know someone arrives somewhere at a certain date and time and leaves at a certain date and time. I calculate the difference in days and hours. The problem is that this means I have to enter date/time in "general" format. This is a lot of data entry for users, since there are at least 5 fields that need to be entered as "05/01/07 05:30". This page doesn't seem to help. I was going to do a Date() for default value, but then if there are any null values, I get a false calculation when I calculate elapsed times. I also thought of using the Calendar macro, by which users could just click on a date, but this then does not allow one to enter the time in the "general" date/time format. Perhaps the solution is here: http://www.thescripts.com/forum/thread525050.html

Here is the module code I found and have been using for my elapsed time calculations:

Option Compare Database
Option Explicit

Public Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function

Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
'*********************************************************************
' Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed between a starting Date/Time and an ending
' Date/Time formatted as a string that looks like this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'*********************************************************************
Dim interval As Double, str As String, days As Variant
Dim hours As String, minutes As String, seconds As String
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes & seconds <> "000", ", ", " "))
' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))
' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ", " "))
' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
ElapsedTimeString = IIf(str = "", "0", str)
End Function

Public Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As String
'*********************************************************************
' Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed in days between a starting Date/Time and
' an ending Date/Time formatted as a string that looks like this:
' "10 days" or "1 day".
'*********************************************************************
Dim interval As Double, days As Variant
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function
interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
ElapsedDays = IIf(days = 1, days & " Day", days & " Days")
End Function


Thanks in advance for any suggestions.

 

[DJkarl]

The Datediff function should work for you

datediff("h",StartDate,EndDate) would return the difference in hours

If you pass the date function as one of the dates it will assume a start time of 12AM (midnight).

 

[hjg]

When I put in the formula you suggest, I get a result that is a date. For instance if the start time is 8/13/2007 9:10:00 AM and the end time is 8/15/2007 5:19:00 AM, the result I get is 2/12/1900. I tried formatting the elapsed time in "general" and "short time" in the table. Perhaps I'm not formatting correctly.

 

[DJkarl]

When I put in the formula you suggest, I get a result that is a date. For instance if the start time is 8/13/2007 9:10:00 AM and the end time is 8/15/2007 5:19:00 AM, the result I get is 2/12/1900. I tried formatting the elapsed time in "general" and "short time" in the table. Perhaps I'm not formatting correctly.

Where are you putting this function, in code, in a form control?

FYI the date that it's returning will evaluate to 44 if you convert it.

Clng(#2/12/1900#)

 

[hjg]

I put this on the form. I used your expression as the control source for the text box "Elapsed Time." Where does the code go?

Thank you for your suggestions.

 

[DJkarl]

I put this on the form. I used your expression as the control source for the text box "Elapsed Time." Where does the code go?

Thank you for your suggestions.

On your Elapsed Time text box I suspect the format is set to a date of some kind. If so then change the format to a number and then the hours should display correctly.

 

[Rabbie]

Just a little reminder on how date/times are stored in Access. They are stored as the number of days since 31 December 1899 with the time as the fractional part so xxxxx.5 would represent 12 Noon on a particular day. .75 would be 6pm etc.

If you are calculating the difference between 2 dates you need to tell Access how you want it displayed. See Access Help for how to format dates/times.

 

[hjg]

Hmm, yes it works. It gives the correct number of hours. Can't seem to get decimal places. Also, not sure if this solves the data entry awkwardness of inputting "05/01/07 05:30". I found a way to join date and hour text boxes, Control Source: =[DocumentDate]+[DocumentTime]. This was also suggested:

Control Source:
=DateSerial(Year(nz([DocumentDate],0)),Month(nz([DocumentDate]),0),Day(nz([DocumentDate],0))+TimeSerial(nz(Hour([DocumentTime],0)),Minute(nz([DocumentTime],0)),Second(nz([DocumentTime],0)))

but doesn't seem to run properly on my form.

Thanks for your help DJKarl

 

[Rabbie]

It gives the correct number of hours. Can't seem to get decimal places.

Another way to get the hours with decimal is to multiply your difference result by 24 and display as a number.

 

[hjg]

DJKarl,

I've put the formula into a query. I've got two fields (really long stupid names): "Total_orders_written_join_qry" and "Total_patient_to_floor_join_query". These are formatted as a general date/time format (01/01/2001 9:00 A.M.). In my new query I'd like to calculate the elapsed time. I've tried simply subtracting one from the other, but that gives hours. (I can multiply by 24, but this isn't the format I wanted.) Below, I've used a DateDiff function, but this also gives hours.

SELECT Total_orders_written_join_qry.TotDtTmOrdWrtn, Total_patient_to_floor_join_query.TotPtntFlr, DateDiff("Short Time",[TotDtTmOrdWrtn],[TotPtntFlr]) AS [Time Elapsed]
FROM Total_orders_written_join_qry, Total_patient_to_floor_join_query
GROUP BY Total_orders_written_join_qry.TotDtTmOrdWrtn, Total_patient_to_floor_join_query.TotPtntFlr;

This produces "#Error" as its result.
I cannot seem to find a way to simply get 2 hours 12 minutes (as an example) as my result.

I think the answer lies somewhere in what all of you have been saying. I'm just not seeing it. I'll keep thinking about it.

Thanks in advance.
Geoff

 

[Rabbie]

Try displaying your elapsed time result as Format(ElapsedTime,"hh:nn") This should display it as Hours:Minutes otherwise try Format(ElapsedTime, "hh Hours nn Minutes").

A Final alternative would be Cint(ElapsedTime*24) & " Hours " & CInt((ElapsedTime*24)-Cint(ElapsedTime*24)*60) & " Minutes"

Hope this helps

 

[hjg]

these solutions don't account for a period longer than 24 hours

The solutions given above (I've tried each of them in a simple query with a start and end date/time in general format) either:

- do not account for periods longer than 24 hours
- account for a period longer than 24 hours but do not give decimal point values (ie 44 hours 24 minutes or 44.45 or however it would be expressed)

The point of this is to look at elapsed time trends. The elapsed times will usually be anywhere from 1-48 hours, so to track variability I need times greater than 24 hours and decimal points on my numbers. I'm amazed that something so mundane is proving so difficult.

Ideally, I'd like to use the format which is on my form, which gives an answer that looks like: 1 day 10 hours 5 minutes. When I put this into a query, though, the PivotChart view only gives counts but not averages. I thought it best to simplify and use some of the formulas listed above, but those have the problems I listed above.

Thanks Rabbie and DJKarl for the suggestions. I'll post a solution if I figure it out...

Geoff