Question

26
Views
Harshada Hadawale (HarshadaH)
HSBC
Harshada Hadawale
HSBC
IN
HarshadaH Member since 2015 3 posts
HSBC
Posted: May 31, 2021
Last activity: June 1, 2021
Posted: 31 May 2021 8:42 EDT
Last activity: 1 Jun 2021 9:55 EDT

Date format conversion issue

Hello 

I am calculating Date difference in seconds in Report Defination by using function and trying to convert in Date HH:MM:SS format in excel sheet. But while exporting there is some issue. Can any body help in this case?

e.g.  Date difference in seconds is  6.74  and it get displayed in excel post export as  "1/6/1900 5:48 pm"

below HTML code  written to calculate difference and convert to expected format.

<%

double elapsedpastdeadline;

String SLADeadline = tools.getActive().getStringValue();

String toDateTime = "";

 String display="";

String StatusWork = tools.getActive().getParentPage().getProperty("pyStatusWork").getStringValue();

    if(StatusWork.contains("Resolved-")){

        toDateTime = tools.getActive().getParentPage().getProperty("pyResolvedTimestamp").getStringValue();

    }else{

        toDateTime = pega_rules_datetime.CurrentDateTime();

    }

      if(SLADeadline!=""){     elapsedpastdeadline = pega_rules_datetime.DateTimeDifference(SLADeadline,toDateTime,"s");

    if(elapsedpastdeadline>0){     /* convert time into HH:MM:SS format */

      double   duration=elapsedpastdeadline/3600.0;

Hello 

I am calculating Date difference in seconds in Report Defination by using function and trying to convert in Date HH:MM:SS format in excel sheet. But while exporting there is some issue. Can any body help in this case?

e.g.  Date difference in seconds is  6.74  and it get displayed in excel post export as  "1/6/1900 5:48 pm"

below HTML code  written to calculate difference and convert to expected format.

<%

double elapsedpastdeadline;

String SLADeadline = tools.getActive().getStringValue();

String toDateTime = "";

 String display="";

String StatusWork = tools.getActive().getParentPage().getProperty("pyStatusWork").getStringValue();

    if(StatusWork.contains("Resolved-")){

        toDateTime = tools.getActive().getParentPage().getProperty("pyResolvedTimestamp").getStringValue();

    }else{

        toDateTime = pega_rules_datetime.CurrentDateTime();

    }

      if(SLADeadline!=""){     elapsedpastdeadline = pega_rules_datetime.DateTimeDifference(SLADeadline,toDateTime,"s");

    if(elapsedpastdeadline>0){     /* convert time into HH:MM:SS format */

      double   duration=elapsedpastdeadline/3600.0;

        int hours=(int)duration % 3600;

         duration=duration-hours;

         duration= duration*60 ;

          int min=(int) duration;

          int sec = (int) ((duration-min)*60) ;

 display=PRNumberFormat.format(null,"00",false, null, hours )+":"+PRNumberFormat.format(null,"00",false, null, min )+":"+PRNumberFormat.format(null,"00",false, null, sec );

          String excelFormat = tools.getParamValue("exportToExcel");

            if(excelFormat=="Yes" ) {

                  /* format as text using excel concatenate function, usgaes =CONCATENATE("text1","text2") */

                    display= "= CONCATENATE(" + "\" "+ display+ "\"" +",\"\" )" ; 

                 } //end if excel formatting

         }      

         } %>

<%= display %>

***Edited by Moderator: Pooja Gadige to add platform capability tag***
Pega Platform 7.3.1 User Experience