DateDiff
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9870
Printed Date: 27 Apr 2024 at 6:03pm
Topic: DateDiff
Posted By: MrCastalum
Subject: DateDiff
Date Posted: 30 Apr 2010 at 1:58am
|
I am usin Crystal Reports XI. A little back story: Basically, we have an SQL server which I have connected to using Crystal Reports. The SQL server is linked to our time and attendance system so basically it logs people's clock in and clock out times. Now, I have group the records by the date (daily), then by user id, and have calculated the maximum and minimum of user id clock in times for that day (to eradicate the problem of people clocking in and out multiple times for lunch breaks etc.) Now I need to calculate the difference between those maximum and minimum times so I know the amount of time they spent here, minus half an hour lunch break. I believe that I would to this using a formula and the operator 'DateDiff'. I actually want my formula to be this: DateDiff (n, Minimum ({Employee.DateTime}, {Employee.UserId}), Maximum ({Employee.DateTime}, {Employee.UserId})) But I get the error message "the ) is missing" and the 'n' is highlighted. It gives me the same message if I just try: DateDiff (n, 29/04/2010 07:48:42, 29/04/2010 16:38:43) Any idea/suggestions? |
Replies:
Posted By: lockwelle
Date Posted: 30 Apr 2010 at 3:14am
|
put the n in quotes like "n"
SQL isn't fussy, but CR is.
HTH
|
Posted By: MrCastalum
Date Posted: 30 Apr 2010 at 3:30am
Fantastic! Two thumbs up! |
Posted By: MrCastalum
Date Posted: 30 Apr 2010 at 3:36am
| One slight problem, I only want to see records when they've worked more than a certain amount of time but the select expert isn't showing any of my formulas? |
Posted By: DBlank
Date Posted: 30 Apr 2010 at 4:00am
|
Formulas that use a function that occurs after teh data is returned cannot be used to limit the selection. You can use a Summarized field that was created with the insert summary function and then in the group portion of the select expert.
You likely will have to try and suppress rather than exclude.
|