Export jalali date to excel in correct format
Export jalali date to excel in correct format
hsoft2020
Posts: 16Questions: 6Answers: 0
Hi. I have some column with jalali date like this : 1403-03-25. when I export it to excel format , the date columns shows like this #####. That cell have date format. when I convert it to general format in excel it turns to a negative number.My table fetches data in ajax mode.
How can I fix it. Or how can I make script export that cell as general format not date format.
This question has an accepted answers - jump to answer
Answers
That probably just means that the column doesn't have enough width to display the date. If you resize the column to increase the width, does it show as expected?
Allan
I resized it. even in larg width it shows ######. if I select the cell in function bar it shows a negative number
Can you link to a page showing the issue so I can take a look please? There is a good chance that it is using the Gregorian calendar (since it fits with the ISO8601 format) and the time stamp for that would be a large negative value.
I don't know how Excel handles Jalali dates - it might need some extra configuration in the XML.
Allan
Hi Allan. thanks for answering.I provide a test link
u can use this link
in second column (ReferDate) there is Jalali format date. if u use export button placed in up left of page in exported excel Date shows like ####.
Thank you very much - I've learned quite a bit from that page!
The issue is happening in the code here. That is attempting to find dates in ISO8601 format and then correctly format them in Excel to be a "date" cell (thus displaying as needed for each locale).
It uses a formatting function to consider into the epoch that Excel uses, and it appears that it is not handling dates before 1900 at all. That seems to be a problem, not just in your own calendar, but also for Gregorian calendar dates prior to 1900 (example). There is an interesting article on the topic here.
I think I'll need to modify my regex there to detect numbers between 1900-9999 only and other dates will need to be treated as strings. That appears to be an Excel limitation.
Allan
I've committed that change and you can see the effect on my example here.
Thank you,
Allan
Hi.Thanks Allan.this is working now.