how to convert duration type field to show HH:MM:SS in excel after exporting

Balakrishna_ABK · ‎02-18-2024

for business elapsed time( duration type) field we are getting integer value after exporting to excel instead of actual value which shows in list view. customer want this to show the value in HH:MM:SS.

Akshay Gupta2 · ‎02-18-2024

In ServiceNow, duration type fields store time durations as integer values, representing the duration in seconds. When exporting records to Excel, ServiceNow includes these numeric values, resulting in the duration field appearing as an integer. However, to present this data in a more human-readable format such as HH:MM:SS, additional steps are required.

**Using Excel Formulas:**

1. **Export Data**: Begin by exporting the desired data from ServiceNow, ensuring that the duration type field is included in the exported dataset.

2. **Open Excel**: Open the exported Excel file containing the data.

3. **Insert Column**: Insert a new column adjacent to the duration field where the converted values will be displayed.

4. **Conversion Formula**: In the newly inserted column, utilize Excel's formula capabilities to convert the integer value into HH:MM:SS format. The formula can be structured as follows:
```
=TEXT(A2/86400, "hh:mm:ss")
```
Here, "A2" represents the cell containing the integer value representing the duration in seconds. Adjust the cell reference based on your dataset accordingly.

5. **Drag Formula**: Drag the formula downwards to apply it to all rows where the conversion is required.

6. **Format Cells**: Select the newly inserted column, right-click, and choose "Format Cells." Under the Number tab, select "Custom" and enter "hh:mm:ss" in the Type field. This ensures that the values are displayed in the desired format.

7. **Save**: Finally, save the Excel file with the converted duration values.

**In ServiceNow Instance:**

1. **Navigate to Table**: Access the table in ServiceNow where the duration field resides.

2. **Create Calculated Field**: Within the table's configuration settings, create a new Calculated Field.

3. **Define Calculation Script**: In the calculation script, utilize JavaScript to perform the conversion. Here's an example script:
```javascript
var durationInSeconds = current.duration_field; // Replace 'duration_field' with the actual name of your duration field
var hours = Math.floor(durationInSeconds / 3600);
var minutes = Math.floor((durationInSeconds % 3600) / 60);
var seconds = durationInSeconds % 60;
return hours + ':' + minutes + ':' + seconds;
```
This script calculates the hours, minutes, and seconds from the duration field and returns them in HH:MM:SS format.

4. **Save Configuration**: Save the field configuration after defining the calculation.

5. **Test and Verify**: Verify the functionality of the newly created calculated field by viewing records in the table. The calculated field should now display the duration in the desired HH:MM:SS format.

By following these comprehensive steps, you can effectively convert the duration type field to HH:MM:SS format either in Excel or directly within the ServiceNow instance. Adjustments can be made as per specific requirements and data structures. If this solution proves useful, your thumbs up is greatly appreciated. Thank you!

View solution in original post

Sumanth16 · ‎02-18-2024

Hi @Balakrishna_ABK ,

This is normal behaviour, though it shows you 14 minutes , but if you open this SLA record and check then it will show you complete details in Days HHMMSS as below.

The permanent fix for this issue :

Check if below property is available or not if not then Create the following sys_property:

1. Go to the system property : sys_properties.list

2. Click on New and add below details

Name: 'glide.ui.duration.seconds.enabled'

Type: true | false

Value: true

(or)

Duration field (the Actual elapsed time column) is formatted as Days, Hours, Minutes (e.g. 838 Days 19 Hours 46 Minutes). This is the expected behavior.

If you want to change or customize anything , here is mentioned couple of ways:

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0656030

Please mark as Correct Answer and Helpful, if applicable.
Thanks!

heathers_ · ‎04-29-2024

Creating a string field and using this calculated script helped me print the duration display value.

 var gr = new GlideRecord("task_sla");
    gr.addQuery("task.number", current.task.number);
    gr.query();
    if (gr.next()) {
        var time = gr.business_duration.getDisplayValue();
        return time;
    }