Date Diff Function   

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

date: Mon, 13 Oct 2008 12:47:01 -0700 (PDT)   author:   unknown

Re: Date Diff Function   

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?

date: Mon, 13 Oct 2008 12:53:42 -0700 (PDT)   author:   unknown

Re: Date Diff Function   

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...

date: Mon, 13 Oct 2008 13:01:22 -0700 (PDT)   author:   unknown

Re: Date Diff Function   

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?

date: Mon, 13 Oct 2008 13:18:23 -0700 (PDT)   author:   unknown

Re: Date Diff Function   

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
'====================================================


nouveauricheinvestments@gmail.com wrote:
> 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?

date: Mon, 13 Oct 2008 19:24:11 -0400   author:   John Spencer