Calculating duration of time spent by data entry person entering data

[jhurley]

I am quite a beginner and have run into a road block:

I have created a data entry report which shows me what data (by customer) has been entered in by specific data entry employees between certain periods of time. What I'd like to do is now create a formula which shows how much time has elapsed between when the first customer's data was entered and when the last customer's data was entered. The fields that tell me the time and date of the entry are "CreatedOn" and "Created At". What confuses me is that if I use a DateDiff formula for example, the fields that I enter in are the same. The data within them, however, change with each customer.

I'd like to be able to determine:
a) how much time has my employee spend overall entering data (this spans several days so I have to know hours and minutes and seconds)
b) how much time does it take (on average) per customer to enter data (minutes and seconds)
c) how much $ then are we spending per customer (which I would determine from the employee's hourly rate)which is a separate formula that will be easy to create I think. Jennifer Hurley
Mediabids.com, Inc.

 

[chris777]

Your not too far off. In your group footer, when you create the formula use datediff("d",minimum(table.field,grouptable.field),maximum(table.field,grouptable.field))

If you use the maximum and minimum function, by the group footer you are in (in this case either customer or employee) it will give you the first and last value in that group.

 

[jhurley]

Thank you very much for your response! I tried the solution you offered and it was fantastic at adding up all the hours/minutes, etc. between two different dates. However, I need to include only times that the data entry person has worked. For example, if they start at 9:05 and end at 6:17 on May 12, 2002 and if they start at 9:00 and end at 4:37 on May 13, 2002, I want to know the time that they have actually worked. My data from Goldmine gives me a time and date for every entry in my database. However, I just need to figure out how much time has actually been spent on Goldmine doing data entry so that I can calculate my cost per entry.
I'm puzzled. Jennifer Hurley
Mediabids.com, Inc.

 

[chris777]

When you create a group for each employee, then a group for each customer, the detail section should contain the start/end fields for each customer record, Correct? If so, simply find the difference for each detail line, then subtotal for each group.

 

[lyanch]

You need to add a "hidden" group that groups by day. Then you can get the datediffs for each day and add them up to complete your calculations for the customer/user groups.

Lisa

 

[jhurley]

Both responses have been very helpful and I have definitely seen ways to use them in other reports, however, the question I have is how to calculate the difference between a begin time and an end time. If I use the datediff for doing this for dates, is there also a function for calculating time elapsed between two separate times?
If there is, I can use the hidden ideas you've mentioned.
Thank you again for all your help. Crystal is overwhelming for a beginner! But, believe it or not, I think I'm catching on! Jennifer Hurley
Mediabids.com, Inc.

 

[chris777]

Subtract your CreatedAt time fields, assuming they are in time format, you will get the difference in total seconds. Then , in the same formula, you do several calculations to extract the hrs:mins:secs from the total seconds. This is then displayed as the output.

The calculations for time format from seconds can be printed from a Crystal KBase article at

http://support.crystaldecisions.com/library/kbase/articles/c2000834.asp

Chris
DFW Crystal User Group

http://www.dfwcug.com