Print Page | Close Window

Crystal DateDiff function

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=20393
Printed Date: 07 May 2024 at 11:32am


Topic: Crystal DateDiff function
Posted By: Jul747
Subject: Crystal DateDiff function
Date Posted: 24 Jan 2014 at 9:59am
I'm using Crystal Reports 11.  I created a new function and trying to find the number of days between two dates.  What I'm getting in the result is a decimal... 4.00    I want to see 4, not 4.00
Here's my formula:
 
DateDiff("d", {Command.RESERVATION_START_DATE}, {Command.RESERVATION_END_DATE})+1
 
The variable names in the function are datetime, but I only want the # of days in integer form, not decimal.
 
I know the "d" should return the # of days between the dates, but it's not working for some reason (at least its not coming in the format I want...Integer.  I've tried INT and ToNumber in front of DateDiff, but no go.
The Reservation_start_date = 9/14/2013, end_date = 9/17/2013 and I want the result to be 4 because the reservation actually starts on 9/14... so the count would be 4...14,15,16,17  (and that's why I added + 1 on the end, otherwise I get 3. 


Replies:
Posted By: lockwelle
Date Posted: 24 Jan 2014 at 12:35pm
have you set the format of the formula...you know right click on the formula on the report and the change the number of decimals?

By default, CR shows all numbers with 2 decimal places. Datediff returns a numbers, so this should fix it

Posted By: kostya1122
Date Posted: 24 Jan 2014 at 1:53pm
or
totext(DateDiff("d", {Command.RESERVATION_START_DATE}, {Command.RESERVATION_END_DATE})+1,0,"")

Posted By: Jul747
Date Posted: 25 Jan 2014 at 11:04am
I did a right-click on the formula on the report and clicked on Format Text...
I get a 5-tabbed window come up and there's nothing showing where you can change the number of decimals. The named tabs are:
 
Common
Border
Font
Paragraph
Hyperlink
 
Is there anywhere else I can look to change the number of decimals in the formula?  It's just not in any of those tabs, looked high and low for it... 

Posted By: Jul747
Date Posted: 25 Jan 2014 at 11:05am
Thanks, but didn't work. Editor says something wrong with the function.

Posted By: lolly54
Date Posted: 26 Jan 2014 at 10:49pm
Like kostya1122 suggested, ToText function should do the work for you.

Try...
ToText(DateDiff("d", {Command.RESERVATION_START_DATE}, {Command.RESERVATION_END_DATE})+1, 0)

Here's a link I found quite useful for ToText
http://crystaltricks.com/wordpress/?p=149

Posted By: Jul747
Date Posted: 27 Jan 2014 at 2:29am
thank you... your were correct except for the "" at the end, didn't need.
Thanks again for your time!

Posted By: Jul747
Date Posted: 27 Jan 2014 at 2:30am
kostya1122 was close.  Didn't need the "" at the end.
What you provided was dead on.  Thank you all very much for your time!


Print Page | Close Window