Hello There,

 Does anyone know how to write a formula in crystal report to find out number of days/month/year between two different dates? I just been to the BB training y'day but did not find it very useful, esp for the formula section.

Your ideas will be very helpful for this assignement.

Kind Regards

Anbu

 

 You should use the DateDiff() function.  It takes three arguments:  The time scale (days, weeks, months, etc.) you want, the first date and the second date.  You should check the help files for the various time scales that you can use.

Drew

 

Hello Mr Drew,

Many thanks for the tip. I just had quick look into the Datadiff function, which would also solve my question but I am just wondering how you would go about resolving the issue I have in RE. I think we had a conversation in RE forum about this, not long time ago.

Our org is change the membership system and from April onwards we won’t have any membership fee, but people can donate if they want and their membership will be active for two years from the last gift date. So my task is to pull out all the lapsed members and check their lapsed data and the last gift date. Given the criterion that they can stay active for two years, i have to calculate the difference the between the dates to find which one is earlier/later and what is the difference. If the difference is more than two years than i will keep them lapsed otherwise i will change them to active.

The logic is to find 'if the difference between two dates is more then two years or less?' I just thought of finding the difference in days and then convert it into years. I don’t know if there is any better idea to go about this issue!

Kind regards

Anbu

Cheers!

 The result of DateDiff() is a signed integer.  In other words, it can return a positive or negative number.  Switching the order of the last two arguments changes the sign.  You actually probably don't want to use this function for your comparison, because it does return an integer, and you want EXACTLY two years, not anywhere from two years up to two years and 364 days.

 I would suggest using the related function DateAdd().  This takes three arguments, the time scale, the number of units, and a date.  You want something like

{Lapsed Date} = DateAdd("y", 2, {Last Gift Date})

This adds two years to the gift date and then compares it to the lapsed date.

It's also best not to try to convert days into years.  It becomes very complicated dealing with leap years.  The DateAdd() and DateDiff() functions already have all of this logic built into them.  There's no sense in reinventing the wheel. 

Drew 

Thats really good way of thinking Mr. Drew. You idea will help a lot.

Cheers!