Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: DateDiff formula help Post Reply Post New Topic
Author Message
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Topic: DateDiff formula help
     Posted: 01 Mar 2013 at 6:46am
CR 9.5 Pro, SQL 2008 database
 
Wanting to compare two dates/times using the formula below but am getting the following error "remaining text does not appear to be part of the formula" and it highlights the second and third formulas. Where am I going wrong?
 
NumberVar logDiff = DateDiff('n',{tblSOLogs.StartDateTime},{tblSOLogs.EndDateTime})
NumberVar hours = logDiff \ 60
NumberVar minutes = logDiff mod 60
 
Sample data:
John Doe - Labor - 2/4/13 7:45 - 2/4/13 8:00 (logDiff = .25)
John Doe - Labor - 2/4/13 8:30 - 2/4/13 10:00 (logDiff = 1.50)
John Doe - Labor - 2/4/13 10:45 - 2/4/13 11:15 (logDiff = .50)
Sub Total: 2.25
 
 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 01 Mar 2013 at 10:44am
First off, when assigning a value to a variable you need to use ":=" instead of "=".  Secondly, when doing a multiple-line formula in Crystal, you need to end each line with a semi-colon - ";".
Also, is it possible for either of the fields to be blank?  If so, you'll need to add some null handling to the formula.  Something like this:
 
NumberVar logDiff := 0;
 
if not IsNull({tblSOLogs.StartDateTime}) and not IsNull({tblSOLogs.EndDateTime}) then
 DateDiff('n',{tblSOLogs.StartDateTime},{tblSOLogs.EndDateTime});
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 04 Mar 2013 at 4:39am
Thank you very much for your help! The formula now looks like this...
 
If not IsNull({tblSOLogs.StartDateTime}) and not IsNull({tblSOLogs.EndDateTime}) Then
NumberVar logDiff := DateDiff('n',{tblSOLogs.StartDateTime},{tblSOLogs.EndDateTime});
NumberVar hours := logDiff \ 60;
NumberVar minutes := logDiff mod 60
 
I inserted the formula into the report and it's not calculating correctly and am not sure where I've gone wrong, sample output below:
 
Mark D - SO   - Log Reason - Start Date  -  End Date    -   Diff
             112    Shop             2-4-13 7:55   2-4-13 9:15    20:00
             115    Travel           2-4-13 9:15   2-4-13 10:15   0:00
             118    Labor            2-4-13 10:15 2-4-13 15:00   45:00
 
 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 04 Mar 2013 at 5:17am
What version of Crystal are you using?  Please go to Help - About and post the full version number.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 04 Mar 2013 at 5:36am
Professional: 10.0.0.533
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 04 Mar 2013 at 6:10am
This is a tough one... I think this was an issue in Crystal 10 that was fixed in a service pack.  But Crystal 10 is out of support and I don't know of anywhere to get the patches for it.
 
I think I know another way to do this.  Are any of your end dates on a different day than your start date?
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
joeg1962
Newbie
Newbie


Joined: 01 Mar 2013
Location: United States
Online Status: Offline
Posts: 35
Quote joeg1962 Replybullet Posted: 05 Mar 2013 at 3:31am
What you are getting as your output appears to simply be a representation of the minutes (without hours) and I would bet this is because it is the last calculation.
I think you will need one more calculation like:
StringVar elapsed := totext(hours, 0, "")& ":" & totext(minutes, "00",0,"")
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 05 Mar 2013 at 8:11am
That seemed to do the trick, thank you VERY much! Lastly how would I go about totaling the logDiff formula for each rep?
 
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 12 Mar 2013 at 6:59am
Just curious if anyone is willing to share as to how I can get subtotats for each rep with logDiff formula?
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.