Conversion of duration field in metric table to hours(93 hours instead of3 Days 22 Hours 57 Minutes)

[Divya13]

I am looking for a recommended solution for the below requirement.

We have configured a metric definition on our extended table  to capture task state change.

The team requires that the resulting metric instance records be reported on a monthly basis.

To address this, I created a database view combining the Metric Instance table and the custom task table to provide a consolidated view as requested. However, the issue we are facing is that the metric instance duration is displayed in days, hours, and minutes (Ex: 3 Days 22 Hours 57 Minutes), but when the report is exported, the value is converted into seconds. The business, however, requires the duration to be displayed in hours.

Approaches Attempted:

1. Attempted to create a function field to calculate the duration in hours, but this is not supported for database view tables and not allowed to create in OOTB table.
2. Tried generating custom csv file with require column a scheduled job script and manually generating an email event and attaching it to an email.
3. Attempted to create a CSV file and using a script to generate the file & then attach it via a business rule before insert on sys email table.

Checking if any one had this scenario or any alternative solution without having to integrate with PowerBI or so.

Any suggestion/solution is highly appreciated or recommended solution between #2 and #3 listed above.

[Tanushree Maiti]

Hi @Divya13 

Solution1: refer: After export as Excel time fields are showing seconds but i want to see it in only Hours and Minutes 

Solution 2: Create a field to store time worked value . Refer: Exporting Time Worked Value to Excel 

Solution 3: refer KB: KB2991795 Duration field displays as an integer when exported from ServiceNow to Excel 

 

Resolution

There are two approaches: convert the values in Excel after export, or create a calculated field in the ServiceNow instance to display the formatted value directly on the record.

Option 1 — Convert duration values in Excel after export

1. Export the desired records from the ServiceNow instance, ensuring the duration field is included in the export.
2. Open the exported file in Excel.
3. Insert a new column immediately adjacent to the duration field column. This column will hold the converted values.
4. In the first data row of the new column, enter the following formula, replacing A2 with the cell reference that contains the integer duration value:

   =TEXT(A2/86400,"dd:hh:mm:ss")

1. Copy the formula down to all remaining rows that contain duration data.
2. Select the new column, right-click, and choose Format Cells.
3. On the Number tab, select Custom, then enter dd:hh:mm:ss in the Type field. Click OK.
4. Save the file.

Option 2 — Create a calculated field in the ServiceNow instance

Note: This option requires instance administrator access. The calculated field displays the converted duration directly on the record and in list views, but does not change the underlying stored value. YOu may need to adjust the code to allow for Day as well as time.

**The code below is considered to be a customization and is not supported by ServiceNow.

1. Navigate to the table where the duration field resides. To open the table configuration, go to System Definition > Tables and search for the table by name.
2. Open the table record and navigate to the Columns tab.
3. Click New to create a new field. Set the Type to Calculated.
4. In the Calculation script field, enter the following script, replacing duration_field with the actual field name of your duration column:

javascript

   var durationInSeconds = current.duration_field;
   var hours = Math.floor(durationInSeconds / 3600);
   var minutes = Math.floor((durationInSeconds % 3600) / 60);
   var seconds = durationInSeconds % 60;
   return hours + ':' + minutes + ':' + seconds;

1. Give the field a descriptive label (for example, Duration (formatted)) and click Submit to save.
2. Open a record in the table and verify that the new calculated field displays the duration in HH:MM:SS format.