Calculating time

[Guest]

I am trying to work out how long it takes for a query to be resovled. I
thought I had worked it out but when it takes more than one day to resolve
the problem I get a negative answer; i.e reponse date & time 16 May 16:00,
resolution date & time 18 May 09:00. If the query is resolved on the same
date then I get the correct answer; 16 May 09:00, 16 May 16:10 gives me 430
minutes. Please can someone let me know whta I am missing from my formula.

(DateDiff("n",([resolution date]-[response date]),([resolution
time]-[response time])))

Many thanks
Diane

 

[Allen Browne]

Try:
DateDiff("n",([resolution date] + [resolutiontime], [response date] +
[response time])

It might be simpler to use a combined date/time field instead of a date
field and a time field.

 

[Guest]

Allen
Thanks very much. I had to put the response time&date before the resolution
time&date as I was getting negative figures. I have inherited the database so
all the fields were already set up.

I have also tried to get the average time but using the AVG in the totals
doesn't seem to change the figures. where am I going wrong? Query also
shows the Department, removing this doesn't help, can you?

Many thanks again.

Diane

Try:
DateDiff("n",([resolution date] + [resolutiontime], [response date] +
[response time])

It might be simpler to use a combined date/time field instead of a date
field and a time field.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to work out how long it takes for a query to be resovled. I
thought I had worked it out but when it takes more than one day to resolve
the problem I get a negative answer; i.e reponse date & time 16 May 16:00,
resolution date & time 18 May 09:00. If the query is resolved on the same
date then I get the correct answer; 16 May 09:00, 16 May 16:10 gives me
430
minutes. Please can someone let me know whta I am missing from my
formula.

(DateDiff("n",([resolution date]-[response date]),([resolution
time]-[response time])))

Many thanks
Diane

 

[Guest]

Allen

I have managed to work out the average time, I just removes all of the
fields except the one you helped me with.

Many thanks again.

Diane

Allen
Thanks very much. I had to put the response time&date before the resolution
time&date as I was getting negative figures. I have inherited the database so
all the fields were already set up.

I have also tried to get the average time but using the AVG in the totals
doesn't seem to change the figures. where am I going wrong? Query also
shows the Department, removing this doesn't help, can you?

Many thanks again.

Diane

Try:
DateDiff("n",([resolution date] + [resolutiontime], [response date] +
[response time])

It might be simpler to use a combined date/time field instead of a date
field and a time field.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to work out how long it takes for a query to be resovled. I
thought I had worked it out but when it takes more than one day to resolve
the problem I get a negative answer; i.e reponse date & time 16 May 16:00,
resolution date & time 18 May 09:00. If the query is resolved on the same
date then I get the correct answer; 16 May 09:00, 16 May 16:10 gives me
430
minutes. Please can someone let me know whta I am missing from my
formula.

(DateDiff("n",([resolution date]-[response date]),([resolution
time]-[response time])))

Many thanks
Diane

 

[Marshall Barton]

I am trying to work out how long it takes for a query to be resovled. I
thought I had worked it out but when it takes more than one day to resolve
the problem I get a negative answer; i.e reponse date & time 16 May 16:00,
resolution date & time 18 May 09:00. If the query is resolved on the same
date then I get the correct answer; 16 May 09:00, 16 May 16:10 gives me 430
minutes. Please can someone let me know whta I am missing from my formula.

(DateDiff("n",([resolution date]-[response date]),([resolution
time]-[response time])))

Are you storing the Date part and the Time part in two
different places??

Generally a Date type field contains both parts, so the
expression would just be:
DateDiff("n", Responsedatetime, Resolutiondatetime)

If you really do have them in separate places, then you can
do this:
DateDiff("n",[response date] + [response time],
[resolution date] + [resolution time])

That will work because a date/time value is a Double with
the date as the number of days since 30 Dec 1899, and the
time part is the fraction of a day (e.g. noon is .5).