Print Page | Close Window

DateDiff Formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13392
Printed Date: 01 May 2024 at 7:42am


Topic: DateDiff Formula
Posted By: VickiM
Subject: DateDiff Formula
Date Posted: 02 Jun 2011 at 7:56am

Hi.

Can you tell me why I am getting an error when trying to save this formula in Crystal Reports XI?
 
DateDiff("yyyy",{EMPLOYEE.DATE_HIRED},Now())+Int(Format(Now(),"mmdd")<Format({EMPLOYEE.DATE_HIRED},"mmdd"))
 
The message I'm getting says I have a ) missing.
Thanks!

-------------
VickiM


Replies:
Posted By: DBlank
Date Posted: 02 Jun 2011 at 8:45am
can you explain what you are trying to get at with the formula?
I am confused by it as the datediff() is looking at years but you are using NOW which is time
also the INT and FORMAT are not making sense to me...

Posted By: VickiM
Date Posted: 02 Jun 2011 at 10:06am

I'm trying to figure Tenure using the person's date of hire.  It works in Access.

I'm getting an error stating: The ) is missing. The first occurrence of the word Format is highlighted so I think it means I need a ) somewhere around that word.  However, no matter where I put one, I get the same message.
 
Thanks!


-------------
VickiM

Posted By: DBlank
Date Posted: 02 Jun 2011 at 10:17am

so you want the number as years hired including this year if the hire date was before or on today but not this year if it was after today?

I think this would give you that...
DateDiff('yyyy',{EMPLOYEE.DATE_HIRED},currentdate) - if datepart('y',currentdate)>datepart('y',{EMPLOYEE.DATE_HIRED}) then 1

Posted By: VickiM
Date Posted: 06 Jun 2011 at 10:28am
Thank you!  That works.  However, I need it to use the month and day as well as the year to figure the tenure. So if they started on 10/1/2009 and today is 6/6/2011 it should show 1 year because they won't actually have been there 2 years until October.  When I type in mmddyyyy in each of the places that have either yyyy or just a y, I get an error that says it's out of range.  Can you tell me why?  Thanks so much!

-------------
VickiM

Posted By: DBlank
Date Posted: 06 Jun 2011 at 11:59am

the second half of the formula is supposed to handle looking at today's date and making sure it has been one full year...try this one:

DateDiff('yyyy',{EMPLOYEE.DATE_HIRED},currentdate)
-
if datepart('y',currentdate)>=datepart('y',{EMPLOYEE.DATE_HIRED}) then 0 else 1

Posted By: VickiM
Date Posted: 08 Jun 2011 at 3:27am
That's perfect!  Thank you very much!!!

-------------
VickiM


Print Page | Close Window