Date formatting and calculation

I have a start time and a complete time in a form to calculate how long it
takes for the form entry. I am using the Now formula in a command button to
get the date and time and it is stored as start time and completion time in a
table.

In a query I want to calculate how long it takes for each record.
DateDiff("n",[start time], [completion time]) isnt working for me.

1) What is the correct formula for this calculation?
2) how do i also get the average completion time for all records?
3) I also want to be able to just select a date range to perform this
calculation ie. Can i use a form or report and enter criteria for the month
of January, feb, March and see what the average completion time is by month,
by week etc?

Thanks!

Hi Belinda

There are a number of ways to do all that you want (use a form to pass the
criteria, enter into Prompts, etc)

These are just a few


1) What is the correct formula for this calculation?
ElapsedTime: DateDiff("n",[TableName]![Start Time],[TableName]![completion
time])

2) how do i also get the average completion time for all records?
Same as above but alter the query to a totals query and select Average in
the Total row of the column


3) I also want to be able to just select a date range to perform this
calculation ie. Can i use a form or report and enter criteria for the month

Add a calculated column with something like this
Month: Format(Month([TableName]![Start Time]),"mmmm")

In the criteria row add this
[What month do you want]


HTH, good luck

--
Wayne
Manchester, England.



"Belinda7237" wrote:

I have a start time and a complete time in a form to calculate how long it
takes for the form entry. I am using the Now formula in a command button to
get the date and time and it is stored as start time and completion time in a
table.

In a query I want to calculate how long it takes for each record.
DateDiff("n",[start time], [completion time]) isnt working for me.

1) What is the correct formula for this calculation?
2) how do i also get the average completion time for all records?
3) I also want to be able to just select a date range to perform this
calculation ie. Can i use a form or report and enter criteria for the month
of January, feb, March and see what the average completion time is by month,
by week etc?

Thanks!

Thanks for your help - I have gotten the first 2 to work beautifully -
however the third is still giving me trouble.

In my query I used:
Month: Format(Month([DA Peer Review]![Start Time]),"mmmm")

DA Peer Review is the table that has the start time in it. And first i ran
it without any criteria and the new field Month as written above populated
with January. All of my test entries are dated with march dates, so it
should have returned March. Does it matter that my current start time
entries use the Now formula therefore look like:

3/14/2008 12:38:30 PM in the cell?


"Wayne-I-M" wrote:

Hi Belinda

There are a number of ways to do all that you want (use a form to pass the
criteria, enter into Prompts, etc)

These are just a few


1) What is the correct formula for this calculation?
ElapsedTime: DateDiff("n",[TableName]![Start Time],[TableName]![completion
time])

2) how do i also get the average completion time for all records?
Same as above but alter the query to a totals query and select Average in
the Total row of the column


3) I also want to be able to just select a date range to perform this
calculation ie. Can i use a form or report and enter criteria for the month

Add a calculated column with something like this
Month: Format(Month([TableName]![Start Time]),"mmmm")

In the criteria row add this
[What month do you want]


HTH, good luck

--
Wayne
Manchester, England.



"Belinda7237" wrote:

I have a start time and a complete time in a form to calculate how long it
takes for the form entry. I am using the Now formula in a command button to
get the date and time and it is stored as start time and completion time in a
table.

In a query I want to calculate how long it takes for each record.
DateDiff("n",[start time], [completion time]) isnt working for me.

1) What is the correct formula for this calculation?
2) how do i also get the average completion time for all records?
3) I also want to be able to just select a date range to perform this
calculation ie. Can i use a form or report and enter criteria for the month
of January, feb, March and see what the average completion time is by month,
by week etc?

Thanks!