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

[Balakrishna_ABK]

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 · Accepted solution]

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!

[Balakrishna_ABK]

Thanks @Akshay Gupta2  for your quick reply.

[heathers_]

Hello, this script is returning an error. How does the hours, minutes, seconds get parsed in the script? It appears to calculating the entire duration field three times.

[Haarishalys]

Hello Akshay! Great this helps. But adding to the same query, if actual elapsed time is like 14 days 16 hours 23 minutes. How do we convert that value into hours in excel? I searched whole internet but unable to find a way