asp & xls

[tom]

hello lads -
got a problem; I made a file that should write down a report into the excel
cells.
the issue is not coming from the sql or summat, but from excel file cannot
convert
the date properly.
if I decided to select 2 dates: the starting date(August for istance) and
the finishing date of the next month(September),
what happens is the finishing date swaps the month with the day .
For Example if I chose to have the report from the 29th August to the 3rd
September what I will have in the cells, will be the following ones:
29/08/2004
30/08/2004
31/08/2004
09/01/2004
09/02/2004
09/03/2004



---> which means is going from the 29th of Aug to the 9th of March instead
of the 3rd of Sept!!! thats no fair :\
I reckon the problem is excel thats sets of default the american date.

- regards tommy

PS: I did set as well session.LCID = 1040 (italian one) and doesn't change.

 

[tom]

Have u got any suggestions about to sort out the problem?
Cause I don't have a clues.
- cheers tommy

 

[Patrice]

What if you try the MM/JJ/AAAA format ? IMO it should be displayed correctly
in Excel and Excel will be happy at import time. It could be even better to
use named months to avoid an ambiguos format.

Patrice

 

[tom]

Cause first in Europe especially Italy we used that format, second my boss
wants like that, third all the users are not so ready to switch into
the american format.thats why, otherwise I would have done straight away.

 

[Bob Barrows [MVP]]

You need to discard the idea that you need to store the date in the same
format in which you are going to display it. Format should be applied when
displayig dates. Dates should be stored in an unambiguous format so that
they can be queried correctly as dates.

Bob Barrows

 

[Patrice]

This is not what I suggested.

I meant that Excel expects IMO a particular format for dates *in* the import
file and IMO this format is MM/JJ/AA.

Once dates are loaded usual formatting rules will apply and dates will be
displayed as usual for your country (I'm French and uses also JJ/MM/AA).
Keep in mind that the problme with dates is that the internal representation
is ok but when you write down a date you have mutliple ways to represent it.
For historical reasons, Excel uses IMO the MM/JJ/AA as its "transport"
format instead of other neutral format used by Internet...

Give it a try and let us know.

Patrice



--

 

[tom]

*visdata is a function that converts a number to date(dd/mm/yyyy)


For Each fld In rs.Fields
if fld.name = "venditore" or fld.name=" " then
xlSheet.Cells(1, col).Value = fld.name
xlSheet.columns(col).NumberFormat = "@"
else
xlSheet.Cells(1, col).Value = cdate(visdata(fld.name))
xlSheet.columns(col).ColumnWidth = 10
end if
col = col + 1
Next

sorted with this issue. the problem was to put a CDATE before scrolling the
fields.
- thanks to everyone

 

[Andrew Zamkovoy]

Hello,

Why not to keep Date fields in Excel format: =DATE(YEAR, MONTH, DATE) ?

With best regards,

 

[Luis]

Have u got any suggestions about to sort out the problem?
Cause I don't have a clues.

Instead of creating an XLS output file generate your output as XML and
save it as a Excel file. Excel will be able to read it ans you can
specify exactly what format you want for each column.

To see what XML you'll require create the type of output that you want
in an Excel spreadsheet and then save the spreadsheet as a HTML page.
Then open the html page in a text editor and you'll get the idea. It
will be quite "bloated" as Excel creates a whole bunch of unnecessary
crap in the HTML page, but it will give you an idea how to approach
it.