SAP Knowledge Base Article - Public

1214148 - How to calculate the difference between two Date Time, and display the result in days, hours, minutes and seconds, in Crystal Reports?

Symptom

  • How to calculate the duration between Date Time fields?
  • Difference between two Date Time fields display the result in days. 
  • How to calculate the difference between Date Time fields in Crystal Reports, and display the result in days, hours, minutes and seconds?

Environment

  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020

Resolution

  • To display the difference between date time in days, hours, minutes and seconds, create a formula:  
            
    1. In Crystal Reports, create a report based on any data source that contains date time fields.
          
    2. Create a formula to calculate the date time difference, and display it in days, hours, minutes and seconds using a code like:   

      Local DateTimeVar StartTime := < INSERT YOUR START DATE TIME FIELD HERE >;
      Local DateTimeVar EndTime := < INSERT YOUR END DATE TIME FIELD HERE >;

      Local NumberVar Days;
      Local NumberVar Hours;
      Local NumberVar Minutes;
      Local NumberVar Seconds;

      Seconds := ABS(DateDiff('s',StartTime,EndTime));
      Days := Truncate( Seconds / 86400,0 );
      Seconds := Seconds - ( Days * 86400 );
      Hours := Truncate( Seconds / 3600,0 );
      Seconds := Seconds - ( Hours * 3600 );
      Minutes := Truncate( Seconds / 60,0 );
      Seconds := Seconds - ( Minutes * 60 );

      ToText(Days,0) + ' Day(s) ' + ToText(Time(Hours,Minutes,Seconds), "HH:mm:ss");

    3. Insert the formula on the report.
        
        
        
    • Note:
      The result of the duration between 2 date time fields can be displayed in different way. Below is another formula example showing the duration in Days, Hours, Minutes and Seconds, in the format: X Days, X Hours, X Minutes, X Seconds 
         
      Local DateTimeVar StartTime := < INSERT YOUR START DATE TIME FIELD HERE >
      Local DateTimeVar EndTime := < INSERT YOUR END DATE TIME FIELD HERE >;

      Local NumberVar Days;
      Local NumberVar Hours;
      Local NumberVar Minutes;
      Local NumberVar Seconds;

      Seconds := ABS(DateDiff('s',StartTime,EndTime));
      Days := Truncate( Seconds / 86400,0 );
      Seconds := Seconds - ( Days * 86400 );
      Hours := Truncate( Seconds / 3600,0 );
      Seconds := Seconds - ( Hours * 3600 );
      Minutes := Truncate( Seconds / 60,0 );
      Seconds := Seconds - ( Minutes * 60 );

      Local StringVar Output;
      Output := If Days > 0 Then ToText(Days,0) + (If Days = 1 Then ' Day ' Else ' Days ');
      Output := Output + (If Hours > 0 Then ToText(Hours,0) + (If Hours = 1 Then ' Hour ' Else ' Hours '));
      Output := Output + (If Minutes > 0 Then ToText(Minutes,0) + (If Minutes = 1 Then ' Minute ' Else ' Minutes '));
      Output := Output + (If Seconds > 0 Then ToText(Seconds,0) + (If Seconds = 1 Then ' Second ' Else ' Seconds '));

Keywords

CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020