Skip to Content
avatar image
Former Member

DateDiff between two date/time fields in different rows

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Jan 19 at 12:34 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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);

  • avatar image
    Former Member
    Jan 19 at 02:08 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 19 at 06:59 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 22 at 01:25 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 22 at 02:08 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 22 at 06:48 PM

    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

    Add comment
    10|10000 characters needed characters exceeded