topic Re: NPrinting Excel Forcing 24 Hour Format in Qlik NPrinting

NPrinting Excel Forcing 24 Hour Format

bbergstrom — Wed, 04 Mar 2020 20:44:49 GMT

Qlik Sense Version: November 2019

NPrinting Version: November 2019

Building an Excel NPrinting template and I have a field that is a Date/Time field. In the Qlik Sense app the data is showing up in a 12 hour format but when that field is pulled into NPrinting and previewed it is switching to a 24 hour format. I've tried applying formatting in the designer but it does not make any difference. I tried clicking on the field and and unchecking Keep Source Formats but all of the formats I'm trying are returning numeric values. Is there something I'm missing?

I attached a image showing the Qlik Sense table on the right and the NPrinting output on the left.

Re: NPrinting Excel Forcing 24 Hour Format

Ruggero_Piccoli — Thu, 05 Mar 2020 08:47:57 GMT

Hi,

You should remove the Keep Source Format and apply the new format in the Excel cells. If this doesn't work open a support ticket, we need to investigate the specific case, because it should work.

You could also force Qlik Sense to export a formatted text as workaround.

Best Regards,

Ruggero

Re: NPrinting Excel Forcing 24 Hour Format

bbergstrom — Thu, 05 Mar 2020 18:40:37 GMT

Do I remove it at the table or field level? I removed it from the field and applied MM/DD/YYYY in the Format field and it's throwing an error saying Row or Column number cannot be zero when trying to preview.

Adding the Keep Source Format back and it previews fine.

Using Excel's formatting on the column does nothing.

Re: NPrinting Excel Forcing 24 Hour Format

bbergstrom — Fri, 06 Mar 2020 15:18:30 GMT

Okay, I think I found a solution but it seems unnecessary. Here are my steps:

Adjusted the loadscript for the field to this: DATE(RequestDt_Local,'MM/DD/YYYY hh:mm tt') AS [Trip Date/Time]
Opened NPrinting and deselected Keep Source Formats
In the cell in Excel (A7 in this case) for the data I manually adjusted the format to mm/dd/yyyy hh:mm AM/PM

This finally produced the desired effect but I'm not sure why these hoops were needed. Here are the errors I ran into:

After step 1 above I tried previewing the report but now I was getting dates but no time, only a colon:

"8/31/2019 :" instead of "8/31/2019 08:32 AM"

Trying to put the format into the properties window either returned an error or reverted back to the 24 hour format.