EasyXLS
http://forum.easyxls.com/

Date format dd/mm/yyyy
http://forum.easyxls.com/viewtopic.php?f=11&t=2276		 Page 1 of 1
Author:  jamesv [ Sat Dec 10, 2022 9:51 pm ]
Post subject:  Date format dd/mm/yyyy
Hello,

I'm having trouble getting dates to consistently come out in "dd/mm/yyyy" format. When the day <= 12, it utilises this as the month.

I've attached 3 files:
- The raw data from SQL Server (the column datatype is "datetime").
- The code I'm using to write to and format the cell using EasyXLS.
- The output in Excel. The orange column is what EasyXLS is outputting; the green column is what I'm expecting based on my ".setFormat.."

Thanks in advance!

Attachments:
Output.png
Output.png [ 6.38 KiB | Viewed 55744 times ]
Code.png
Code.png [ 5.54 KiB | Viewed 55744 times ]
Table Data.png
Table Data.png [ 3.93 KiB | Viewed 55744 times ]
Author:  oana [ Wed Dec 14, 2022 5:58 am ]
Post subject:  Re: Date format dd/mm/yyyy
Hi,

You need to extract data from sql in "mm/dd/yyyy" format and then set the data type as DataType.DATE for best performances. Other similar date formats are recognized as M/d/y or M-d-y.

A date value is a calendar date that can also include the time. We recommend entering the date values in "mm/dd/yyyy" format (i.e. 12/23/2005) for optimization purposes. Later, to apply a different format, the cell can be formatted using the desired date format, as you did.

You might want to check this chapter in our online User Guide
https://www.easyxls.com/manual/basics/i ... ate-values
Author:  jamesv [ Wed Dec 14, 2022 4:59 pm ]
Post subject:  Re: Date format dd/mm/yyyy
Thanks oana! Using your suggestion I was able to fix by reorganising the date format in the Classic ASP to "mm/dd/yyyy" before writing to the spreadsheet, and this fixed it <- src="{SMILIES_PATH}/icon_smile.gif" alt=":)" title="Smile" />

Attachments:
Updated Code.png
Updated Code.png [ 9.52 KiB | Viewed 55721 times ]
Author:  oana [ Thu Dec 15, 2022 4:33 am ]
Post subject:  Re: Date format dd/mm/yyyy
That is good news. You are very welcome, James!
Page 1 of 1 All times are UTC - 4 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/