Incorrect time (hh:mm) format when export the data into excel sheet

[ChaithraMahesh]

Hi All,

When exporting data that includes a time column formatted as 'hh:mm' into an Excel sheet, I encounter an issue where the format is not preserved correctly. 

Requirement- 

• Time Format Preservation: The exported data should maintain the 'hh:mm' format as shown in the QlikSense report.

• Table Header: The exported table should include "Total" at the top.
• Color Formatting: The color formatting in the "Flag" column should also be preserved.

Here's the situation and what I've tried so far:

Expression: Total Hours = interval(sum(Hours),'hh:mm')

1. Export with Table Formatting Enabled:

When exporting data with "Table Formatting" enabled, the time column "Total Hours" displays in a 24-hour format instead of the intended 'hh:mm' format.

For instance, a total of "45:21" hours appears as "21:21" after subtracting 24:00 hours

[ (45:21) - (24:00) = (21:21)]

2. Export without Table Formatting:

Exporting without enabling "Table Formatting" preserves the time format as 'hh:mm'. However, this method causes other issues, such as the loss of color formatting in another column called "Flag."

I attempted using the "Text" function instead of just the "Interval" function to achieve the desired export format. Here’s what I did and the issue that arose:

Attempted Solution

• Expression Used: Total Hours = Text(interval(sum(Hours),'hh:mm'))
• Result: This approach allowed me to export the data with "Table Formatting" enabled, preserving the 'hh:mm' format as required.

Issue Encountered

• Calculation Problem: When this format is used, the total hours displayed at the bottom of the Excel sheet only show the count of records, not the sum of the selected records. For example, if the user selects records 4th and 5th row in excel, the bottom of the sheet displays a count (highlighted in yellow) rather than the sum. Additionally, the top of the page shows the format as "General," making it difficult for users to calculate the total hours for selected records.
• User Frustration: Business users have to manually convert these text values into hours, which involves complex calculations and is frustrating for them.

Requirement

• The export should display the total hours in 'hh:mm' format while allowing users to easily calculate totals for selected records. Ideally, users should be able to see the sum of selected rows in the Excel sheet without additional complex steps.

Hence, I need a solution to export the data so that the "Total Hours" column maintains the 'hh:mm' format, while also retaining color formatting in the "Flag" column if "Table Formatting" in either ways enabled or not enabled and also enable business see the custom "Sum" of the records.

[Fran_by]

I have same problem!
QS May 2024 Patch 3
but I think it was in all versions.