Date Diff Function

  • Thread starter Thread starter nouveauricheinvestments
  • Start date Start date
N

nouveauricheinvestments

Hi,

I have two columns. each column has a date and a time. i.e. a date
when the ticket was opened and a date when it was closed. I want to a
third column to automatically tell me the difference in hours between
the other two columns. This is what I am trying to use and it is not
working.

DateDiff("h","Start Time","End Time")

Start Time and End Time are the names of my two columns I want to
calculate the difference between.

I have the calculated column in the format of number. What should I
do differently?

Thanks
 
Maybe I am not understanding the fundamentals of Access yet...Should I
be doing this with a query and just be storing the raw data in the
table?
 
Okay I just did it in a query...It is giving me a decimal number
though and I tried to group it by system like this:

GROUP BY: Manager

But I don't think it worked cause I am seeing multiple instances of
the same thing...
 
Okay I figured out how to reformat it. I am having the same problem I
have in excel though. It is giving me the hour residual value AFTER
the number of days. So it is not giving me an accurate number.

i.e. I have

Start Time End Time
2007.01.12 14:00 2007.01.16 12:33

It is calculating a difference of 22:33. How can I fix that?
 
DateDiff("h",[Start Time],[End Time])
Will give you the number of hour boundaries crossed. For instånce 13:59
to 14:01 will return 1 hour.

If you have an extended period and want hours and minutes use dateDiff
to get the minutes and then do some math.

DateDiff("n",[Start Time],[End Time]) will return the number of minutes so


DateDiff("n",[Start Time],[End Time])\60 will give you the hours
and
DateDiff("n",[Start Time],[End Time]) mod 60 will give you the remaining
minutes.

Combine them to get a string that looks like hours and minutes.

DateDiff("n",[Start Time],[End Time])\60 & ":" &
Format(DateDiff("n",[Start Time],[End Time]) mod 60,"00")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top