How to calculate days between corresponding to a specific date

[Former Member]

Hi All,

I have the following table structure in my database:

Customer No.         Status            Date

    123                       1               01/01/2016

    123                       2               01/05/2016

    123                       3               01/11/2016

    123                       1               01/15/2016

    123                       5               01/21/2016

    123                       3               01/25/2016

I want to be able to calculate the days between when the Status changed from '1' to '3' for a specific customer.

In the example above, the status changed from '1' to '3' twice from 01/01/2016 to 01/11/2016 and then from 01/15/2016 to 01/25/2016.

Hence the days would be 11 + 10 = 21 days.

Will it possible to calculate this using a formula in crystal reports?

Thanks

[abhilash_kumar · Accepted solution]

Hi,

Try this please:

1) Insert a group on the Customer No. field

2) Create a formula with this code and place it on the Details:

shared numbervar diff;

shared datevar start;

shared datevar end;

if {Sheet1_.status} = 1 then

    start := date({Sheet1_.Date})

else if {Sheet1_.status} = 3 then

(   

    end := date({Sheet1_.Date});

    if start <> cdate(0,0,0) then

    (

        diff := diff + DateDiff('d',start,end);

    );

    end;

);

'';

3) Create a formula to display the difference in days and place this on the Group Footer:

shared numbervar diff;

4) Create a reset formula and place this on the Group Header:

shared numbervar diff := 0;

shared datevar start := cdate(0,0,0);

shared datevar end := cdate(0,0,0);

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thank you for the reply. I used your formulas in the report but my days difference comes out to be 0.

If i modify my problem as seen below where Date 1 is only capturing Status = 1 date and Date 2 is capturing Status = 3 date

Customer No.         Status            Date 1          Date 2

    123                       1               01/01/2016

    123                       2               

    123                       3                                       01/11/2016

    123                       1               01/15/2016

    123                       5           

    123                       3                                       01/25/2016

Now i need to be able to get the days between Date 1 and Date 2 and then add them to get a combined total for the days difference. In this case it will be 11+10 = 21 and subtract it from 25 (i.e. the first occurrence of Date 1 - last occurrence of Date 2)

Hence the answer will be 25 - 21 = 4

How can i write a formula that captures and adds the days difference between Date 1 and Date 2?

[abhilash_kumar · Accepted solution]

Not sure why you get zeroes because this same dataset shows the correct date diff for me!

If you want to subtract the last part, modify the formula on the details to:

shared numbervar diff;

shared datevar start;

shared datevar end;

shared datevar dd1;

shared datevar dd2;

if dd1 = cdate(0,0,0) AND {Sheet1_.status} = 1 then

     dd1 := {Sheet1_.Date};

if dd2 = cdate(0,0,0) {Sheet1_.status} = 3 then

     dd2 := {Sheet1_.Date};

if {Sheet1_.status} = 1 then

    start := date({Sheet1_.Date})

else if {Sheet1_.status} = 3 then

(  

    end := date({Sheet1_.Date});

    if start <> cdate(0,0,0) then

    (

        diff := diff + DateDiff('d',start,end);

    );

    end;

);

diff := diff - DateDiff('d',dd1,dd2);

'';

You'd then need to add these two variables to the reset formulas as well;

shared numbervar diff := 0;

shared datevar start := cdate(0,0,0);

shared datevar end := cdate(0,0,0);

shared datevar dd1 := cdate(0,0,0);

shared datevar dd2 := cdate(0,0,0);

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I am trying to use this formula but keep getting an error that says

"A date is required here" in all the date formulas in the code e.g. i get this error in the formula for dd1 := {Sheet1_.Date};

[abhilash_kumar · Accepted solution]

Use:

dd1 := Date({Sheet1_.Date});

Do the same for dd2.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I am still getting a null value after using the above formula. One idea i can think of is to capture the date for every change of status and then create a formula to get the days differences between these dates.

Do you know how can i retrieve the date corresponding to a status?

e.g. in my original question, how can i return the date 01/11/2016 for the status = 3 and so on?

[abhilash_kumar · Accepted solution]

I used this same sample dataset in a report and it works fine for me.

Could you send your report 'with saved data' to my e-mail and I'll see what's happening?

-Abhilash

[Former Member]

Hi Abhilash,

I have sent an email with the problem description and sample data to your email address. I look forward to hear back from you.

Thanks,

Nimish

[abhilash_kumar]

Hi Nimish,

I used the sample excel sheet you sent to me and used the code from my first reply.

I get 13 days as expected.

I sent you the report too.

-Abhilash

[Former Member]

Hi Abhilash,

I sent you the exact crystal file that I am having trouble with.

Please let me know the results of your analysis.

Thanks,

Nimish

[abhilash_kumar]

Hi Nimish,

I just sent you the updated report using the same formula I posted above..

Works fine!

-Abhilash

[Former Member]

Thanks a lot Abhilash for all your help!