If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Date formatting and calculation
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! |
#3
|
|||
|
|||
Date formatting and calculation
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! |
Thread Tools | |
Display Modes | |
|
|