datediff rersult

[Pietro]

Hi all,

I used the "datediff" function to display the difeerence between two
dates (formatted as general date) in hours.
The result appears like this 25.333 i want it to appear like this
dd:hh:nn:ss

Thanx

 

[Daniel Pineault]

Hopefully one of these article will shed some light on the subject for you!

How to store, calculate, and compare Date/Time data in Microsoft Access
http://support.microsoft.com/?id=210276

On time and how much has elapsed
http://office.microsoft.com/en-us/assistance/HA011102181033.aspx

Using dates and times in Access
http://office.microsoft.com/en-us/assistance/HA010546621033.aspx

How to Calculate Daily Hours Based on Clock In/Clock Out Times
http://support.microsoft.com/?id=237958

Start with the first article, you should be able to use the 'ElapsedTime'
function with a little mod.

 

[Ken Sheridan]

The following function will do this. If a value of True is passed into it as
the optional blnShowDays argument it shows the days, if not the total hours,
so:

TimeDuration(StartDateTime, EndDateTime,True)

might return:

2 days 2:57:07

while

TimeDuration(StartDateTime, EndDateTime)

would return:

50:57:07

It would be trivial task to amend it to return the format dd:hh:nn:ss.

BTW the date format is irrelevant. Date/time values in Access are stored
as a 64 bit floating point number with the integer part representing the days
and the fractional part the times of day, with an origin at 30 December 1899
00:00:00; the format is merely how you choose to see them. Here's the
function:

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

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds 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
strMinutesSeconds = Format(dblDuration, ":nn:ss")

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

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

End Function

Ken Sheridan
Stafford, England

 

[Daniel]

Just one precious, as I had an issue with it recently with similar code,
because the code uses the Const HOURSINDAY = 24 (which is not always true)
it will return an erroneous value when the time changes (daylight savings).

Daniel Pineault

 

[Ken Sheridan]

I must admit that had never occurred to me. The DateDiff function doesn't
allow for it either, of course, and as the dates differ from country to
country, its difficult to see how one could have a generic solution. A table
of the start and end of DST for each country over a period of years which the
function could reference would probably be the answer. When I've no
interesting paint to watch dry I'll maybe give it a try, for Europe and the
USA at least.

Ken Sheridan
Stafford, England

 

[Pietro]

I tried the below code ,but it gives me error :"syntax error (coma) in wuery
expression"

 

[Ken Sheridan]

The error is more likely to be in wherever you are calling the function. I
know the function itself works, subject to the caveat which Daniel has
pointed out regarding DST.

Are you calling it in a query or in an expression in a form or report? If
in a query you might have something like this for instance:

SELECT [ProjectName],
TimeDuration([ProjectStartDateTime], [ProjectEndDateTime], True)
AS [ProjectDuration]
FROM [Projects];

where ProjectStartDateTime and ProjectEndDateTime are the names of columns
of date/time data type in a table Projects. In query design view you'd call
the function by entering the following in the 'field' row of a blank column
in the design grid:

ProjectDuration:TimeDuration([ProjectStartDateTime], [ProjectEndDateTime],
True)

Ken Sheridan
Stafford, England