Reports exported to Excel must show Hrs/Min/Seconds (S)

[doanh]

I have a issue when reports exported to Excel with date/time fields

this is a report

when i export to excel i received:

you can see the difference of date/time fields in both images

Question is how to be able to export the file to excel format and have date/time fields display in Hours/Minutes/Seconds instead of having the spreadsheet display in seconds   ? Do you have any approach in script ?

(note: this is not a bug of ServiceNow because this is a design by them )

[abilash_017]

It is a known error in servicenow.

As Mohamed said, create a string field, and hide it on the form. Use that field only in reporting.

Follow these next steps:

1.To sync present data write a background script .

2.for future records, write a business rule.

Thanks.

[Adiseshu Borra]

Hi,

The Actual Elapsed Time field is duration type.

Below are the steps to show elapse time in days format:

Step 1) create a new string field on task_sla

Step 2) write a new business rule on task_sla

  Conditions:   Actual Elapsed Time changes

  Type: before update

  Advanced Script:

Step 3) personalize list view by adding new field

Step 4) Right click and export to Excel.

Step 5) Type the below text into the background script window and it will update exiting records.

[priyaranjani]

Hello,

Your suggestion really works. But the issue is i am not able to get the seconds of the duration field while getting the display value.

Above example shows the seconds value is getting discarded. Any idea how to overcome this?

[Hadeer]

Hi Adiseshu, 

how can i create new string field on task_sla ?

[RichardSaunders]

Try this background script to update records for the last 2 years, bypassing any business rules.

uncomment out ela.update();

var ela = new GlideRecord ('task_sla');
ela.addQuery('u_elapsed_timeISEMPTY^sys_created_onONLast 2 years@javascript:gs.beginningOfLast2Years()@javascript:gs.endOfLast2Years()');
ela.setLimit(40);
ela.query();
while(ela.next())
{

ela.u_elapsed_time=ela.duration.getDisplayValue();


ela.autoSysFields(false);                                  
ela.setWorkflow(false);
ela.setUseEngines(false);
//ela.update();
gs.print('SLA ' + ela.sla.getDisplayValue() + ' task ' + ela.task.number + ' Elapsed ' + ela.u_elapsed_time);
}