FAQ: Formatting Durations for Excel

When I export CSV, durations appear as numbers, which Excel doesn't understand. How can I format durations to work with Excel?

Short Answer

Add an extra column to the spreadsheet to convert them to fractional days; or use a custom database field in the report element.

Long Answer

Excel represents durations in days, so "1" is one day, and "1/24" is one hour. But Sawmill represents them as seconds for some log formats, milliseconds for others, and microseconds for a few. To format them as durations in Excel, they must be converted. This can be done either after the export, in Excel, or before the export, in Sawmill.

Formatting After the Export

The easiest way, in most cases, is to add a new column in the exported spreadsheet, to convert between the units. For instance, if column E is the "time taken" field in milliseconds, create a new column with formula "=En/(1000*24*60*60)" where n is the row number, and fill down to populate the whole column. This will create a column whose values are "time taken" in days. Then format the cells of that column to use any "time" format, and it will be formatted as a time, in hour, minutes, seconds, etc.

Formatting as Part of the Export

If formatting after the export is not possible, or not efficient, you can do the conversion in Sawmill, but it's considerably more involved.

For this example, we'll assume we're dealing with the "time-taken" field in IIS web logs, called time_taken in Sawmill.

1. Create a database field with a custom expression.

This custom expression is to format the time-taken value in the standard duration_milliseconds format of Sawmill. Do this by editing the profile CFG file (in LogAnalysisInfo/profiles) with a text editor, finding the time_taken database field. Search for "database = {"; then search downward from there for "fields = {"; then search downward from there for "time_taken = {"), and duplicating it, adding a time_taken_excel_format database field underneath the time_taken database field:

      time_taken_excel_format = {
        label = "time taken (Excel format)"
        type = "string"
        log_field = "time_taken"
        display_format_type = "duration_milliseconds"
        expression = `format(cell_by_name(row_number, 'time_taken'), 'duration_milliseconds')`
      } # time_taken_excel_format

2. Add this as a column to the report you'll be exporting. For instance, if the report is the hour_of_day report, find its column in the CFG file by searching from the top for "statistics = {", then searching down from there for "reports = {", then searching down from there for "file_type = {"; then searching down from there for "columns = {". Copy the time_taken column, and edit the duplicate to look like this:

              time_taken_excel_format = {
                header_label = "time taken (Excel format)"
                type = "string"
                show_number_column = "true"
                show_percent_column = "false"
                show_bar_column = "false"
                visible = "true"
                field_name = "time_taken_excel_format"
                data_type = "string"
                display_format_type = "duration_milliseconds"
              } # time_taken_excel_format

3. Rebuild the database; then when you export this report, it will include a new "time taken (Excel format)" column, with standard Sawmill duration formatting ("Y years, D days, HH:MM:SS.MMM").