topic Re: Date does not show correctly in App Development

Date does not show correctly

Daniel1908 — Thu, 02 Dec 2021 14:50:25 GMT

Hello Together,

the date in my excel file shows 31.12.9999.
If I load the data I shows 2957003 in the table.

I already added the below to the script.

Date( "End Date",'DD.MM.YYYY') as "End_Date_2",

No I am getting 30.12.9995 instead of 31.12.9999

Thanks for you help.

Kind Regards,
Daniel

Re: Date does not show correctly

Or — Thu, 02 Dec 2021 15:03:06 GMT

I think your issue is actually with Excel not calculating the date's numeric value correctly. Oracle SQL also returns 9995 in this scenario:

select to_char(
to_date('1899-12-30', 'YYYY-MM-DD') + 2957003,
'DD/MON/YYYY') from dual

Return: 30/DEC/9995

So if there's an issue here that's not in Excel, it seems to be consistent across multiple platforms and not just Qlik. I'm no expert on this, since dates for the year 9999 aren't something I've had to worry about...

As a workaround, you could try looking at this as a string instead and creating your own date by breaking down the components:

MakeDate(Right("End Date",4),Mid("End Date",4,2),Left("End Date",2))

Re: Date does not show correctly

marcus_sommer — Thu, 02 Dec 2021 15:15:06 GMT

The reason for this behaviour is that you are not using the default date-system in Excel else the 1904 mode. Quite often that's done to calculate with negative times or similar matters but it has serious side-effects especially as it isn't a document-setting in Excel else it's a user-property.

Therefore I suggest to consider to change your Excel settings. Be aware that this has an impact on all your files - maybe also if your work with colleagues. So now might be not the right time and it may be easier to remain by your settings. But in the long-term it will probably cause more troubles as it solved.

If you want to remain by it you need to add an offset value of 1462 to your dates. More background to the reasons could you find here:

Differences between the 1900 and the 1904 date system - Office | Microsoft Docs

- Marcus

Re: Date does not show correctly

Daniel1908 — Thu, 02 Dec 2021 16:41:50 GMT

I disabled 1904 mode unfortunately no changes.

Re: Date does not show correctly

marcus_sommer — Fri, 03 Dec 2021 06:25:54 GMT

Because of the fact that it was not recommended to apply this date mode I never used it personally. Therefore I'm not sure how the data are finally stored. I think if you google for enabling/disabling the feature and it's impact you will learn what's happened and what you may need to do to reverse the data respectively to get the wanted results. If there are no inbuilt methods I wouldn't be surprised if there any macros available which may do the job.

Personally I could imagine that the real stored date-values - which are always pure numeric values - are the "normal" value - 1462 and the displaying on the screen adds each time the 1462 offset to it and showed it within your chosen formatting.

- Marcus