DateDiff between two date/time fields in different rows

[former_member275473]

CR 2016

I have a report that I am trying to calculate the time difference but the fields show up in different rows. I have attached all my formula build for review.

@Time diff

datediff("h",{@Cosigntime},{@Signtime})

@Cosigntime

select {NOTE_EDIT_TRAIL.LINE} case {@CosignLine} :{NOTE_EDIT_TRAIL.IP_ACTION_DTTM}

@Signtime

select {NOTE_EDIT_TRAIL.IP_ACTION_ON_NOTE_C} case 2 :{NOTE_EDIT_TRAIL.IP_ACTION_DTTM}

@CosignLine

select {NOTE_EDIT_TRAIL.IP_ACTION_ON_NOTE_C} case 7 :{NOTE_EDIT_TRAIL.LINE}

time-diff-formula-between-lines.jpg

[former_member292966]

Well done.

You can't reference a Text box inside a formula but you can put the text into the formula. If you don't need to see the things in GH3 and Detail sections add 2 more formulas to show the results for the formula like:

@ShowCosign

WhilePrintingRecords; 
DateTimeVar CosignTime; 

"Cosign Time " & ToText (CosignTime, "MM/dd/yyyy HH:mm:ss"); 

@ShowSign

WhilePrintingRecords; 
DateTimeVar SignTime;

"Sign Time " & ToText (SignTime, "MM/dd/yyyy HH:mm:ss"); 

Drop these into the same section as@Time Diff. Format GH3 and Details to hide. Don't format to suppress because that can cause Crystal to not run the formulas.

Good luck,

Brian

[former_member275473]

Brian,

I think I have it working now. final-formula-working.jpg

I have attached results. Is there a way to embed a text box in the formula? Trying to remove the duplicate text boxes.

final-formula-results.jpg

[former_member275473]

Brian,

I have attached where the formulas are located in report. I have also attached the values the formulas are pulling.

formula-locations.jpgformula-output.jpg

[former_member292966]

Hi Doug,

In the last screenshot, are these rows in a Group Footer?

Crystal can't do the @Time formula on the same row as the @SignTime and @CosignTime formulas. It has to do it in a section below. Crystal only processes formulas from right to left and top down. @CosignTime should work because it will have gotten the proper datetime from @SignTime because @SignTime happens before @CosignTime. @SignTime would get a strange number because it does have a value for @CosignTime yet.

The only way @Time can work would be to drop it into a section below @CosignTime and @SignTime. The Report Footer or the Group Footer outside the current group.

Hope this makes sense,

Brian

[former_member275473]

Brian,

Thanks for your help! I would expect comparing Line 2 and Line 7 would give me two hours.

I updated my formulas to what you recommend and report pulled the following:

sap-formulas.jpg

[former_member292966]

Hi Doug,

The formulas won't pass a value between records with you using a variable. Try this:

@Time 
WhilePrintingRecords; 
DateTimeVar CosignTime; 
DateTimeVar SignTime; 

diffdatediff("h",CosignTime,Signtime)

@Cosigntime
WhilePrintingRecords; 
NumberVar LineSeven; 
DateTimeVar CosignTime; 
select {NOTE_EDIT_TRAIL.LINE} case LineSeven :CosignTime := {NOTE_EDIT_TRAIL.IP_ACTION_DTTM}

@Signtime 
WhilePrintingRecords; 
DateTimeVar SignTime; 
select {NOTE_EDIT_TRAIL.IP_ACTION_ON_NOTE_C} case 2 :SignTime := {NOTE_EDIT_TRAIL.IP_ACTION_DTTM}

@CosignLine
WhilePrintingRecords; 
NumberVar LineSeven; 
select {NOTE_EDIT_TRAIL.IP_ACTION_ON_NOTE_C} case 7 :LineSeven := {NOTE_EDIT_TRAIL.LINE}

If these formulas are in a group and you need to reset the calculations at the start of each group, initialize the variables in the group header like:

@initialize
WhilePrintingRecords; 
NumberVar LineSeven := 0; 
DateTimeVar SignTime := DateTime (0, 0, 0, 0, 0, 0); 
DateTime CosignTime := DateTime (0, 0, 0, 0, 0, 0); 

Good luck,

Brian

[former_member275473]

Brian, how and where would I put in the @initialize formula. I need to do that since when there is no cosigntime it calculates from the previous cosigntime.

Thanks

initialize-formula-location.jpg

[former_member275473]

I think I figured it out. I put the below formula in the GH#3. The formula wouldn't save since the last text DateTime was missing Var.

WhilePrintingRecords;

NumberVar LineSeven := 0;

DateTimeVar SignTime := DateTime (0, 0, 0, 0, 0, 0);

DateTimeVar CosignTime := DateTime (0, 0, 0, 0, 0, 0);