Calculate "Year" from Start_Date and End_Date date range.

[R000k]

Hi there,
I am a Crystal newbie and I need to produce a cross-tab report that returns a "Year" column with the "Yearly rent" below. An example of data I am working with is below:

Lease Name Lease_StartDate Lease_EndDate Monthly Rent
Lease A 1-Nov-09 30-Jun-12 $200.00
Lease A 1-Jul-12 29-Jun-13 $300.00

I need to be able to get for example, to have the column of the report say "Year 2010" and below that have $2400. I'm not sure if I can do all of this within Crystal. If I can, what kind of forumula do I need to create.

Any ideas greatly appreciated.

Thanks!

 

[Madawc]

Something like

ToText({your.date}), "yyyy")

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it makes no difference.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[R000k]

Thanks Madawc for your feedback. I am currently using Crystal version 9.

 

[lbass]

You need formulas something like this (one for each year):

//{@Year 2010}:
local datevar start;
local datevar end;
if {table.leasestartdate} <= date(2010,1,1) and
{table.leaseenddate} > date(2010,1,1) then
start := date(2010,1,1) else
if {table.leasestartdate} in date(2010,1,1) to date(2010,12,31) then
start := {table.leasestartdate};
if {table.leaseenddate} > date(2010,12,31) and
{table.leasestartdate} <= date(2010,12,31) then
end := date(2010,12,31) else
if {table.leaseenddate} in date(2010,1,1) to date(2010,12,31) then
end := {table.leaseenddate};
(datediff("m",start,end)+1)*{table.monthlyrent}

-LB