473,324 Members | 2,254 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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.
Jul 19 '05 #1
8 1578
tom
Have u got any suggestions about to sort out the problem?
Cause I don't have a clues.
- cheers tommy
"tom" <tg***@libero.it> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
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.

Jul 19 '05 #2
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" <tg***@libero.it> a écrit dans le message de
news:%2****************@TK2MSFTNGP15.phx.gbl...
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.

Jul 19 '05 #3
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.

"Patrice" <no****@nowhere.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
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" <tg***@libero.it> a écrit dans le message de
news:%2****************@TK2MSFTNGP15.phx.gbl...
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.


Jul 19 '05 #4
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
tom wrote:
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.

"Patrice" <no****@nowhere.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
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" <tg***@libero.it> a écrit dans le message de
news:%2****************@TK2MSFTNGP15.phx.gbl...
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.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 19 '05 #5
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" <tg***@libero.it> a écrit dans le message de
news:uc*************@TK2MSFTNGP14.phx.gbl...
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.

"Patrice" <no****@nowhere.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
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" <tg***@libero.it> a écrit dans le message de
news:%2****************@TK2MSFTNGP15.phx.gbl...
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.



Jul 19 '05 #6
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


"Patrice" <no****@nowhere.com> wrote in message
news:en**************@TK2MSFTNGP15.phx.gbl...
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" <tg***@libero.it> a écrit dans le message de
news:uc*************@TK2MSFTNGP14.phx.gbl...
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.
"Patrice" <no****@nowhere.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
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" <tg***@libero.it> a écrit dans le message de
news:%2****************@TK2MSFTNGP15.phx.gbl...
> 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.
>
>



Jul 19 '05 #7
Hello,

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

With best regards,

"tom" <tg***@libero.it> wrote in message
news:O5**************@TK2MSFTNGP11.phx.gbl...
Have u got any suggestions about to sort out the problem?
Cause I don't have a clues.
- cheers tommy
"tom" <tg***@libero.it> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
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.


Jul 19 '05 #8
On Tue, 28 Sep 2004 14:30:55 +0200, "tom" <tg***@libero.it> wrote:
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.

Jul 19 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Collin VanDyck | last post by:
I have a basic understanding of this, so forgive me if I am overly simplistic in my explanation of my problem.. I am trying to get a Java/Xalan transform to pass through a numeric character...
1
by: DrTebi | last post by:
Hello, I have the following problem: I used to "encode" my email address within links, in order to avoid (most) email spiders. So I had a link like this: <a...
0
by: Thomas Scheffler | last post by:
Hi, I runned in trouble using XALAN for XSL-Transformation. The following snipplet show what I mean: <a href="http://blah.com/?test=test&amp;test2=test2">Test1&amp;</a> <a...
4
by: Luklrc | last post by:
Hi, I'm having to create a querysting with javascript. My problem is that javscript turns the "&" characher into "&amp;" when it gets used as a querystring in the url EG: ...
4
by: johkar | last post by:
When the output method is set to xml, even though I have CDATA around my JavaScript, the operaters of && and < are converted to XML character entities which causes errors in my JavaScript. I know...
8
by: Nathan Sokalski | last post by:
I add a JavaScript event handler to some of my Webcontrols using the Attributes.Add() method as follows: Dim jscode as String = "return (event.keyCode>=65&&event.keyCode<=90);"...
11
by: Jeremy | last post by:
How can one stop a browser from converting &amp; to & ? We have a textarea in our system wehre a user can type in some html code and have it saved to the database. When the data is retireved...
14
by: Arne | last post by:
A lot of Firefox users I know, says they have problems with validation where the ampersand sign has to be written as &amp; to be valid. I don't have Firefox my self and don't wont to install it only...
12
by: InvalidLastName | last post by:
We have been used XslTransform. .NET 1.1, for transform XML document, Dataset with xsl to HTML. Some of these html contents contain javascript and links. For example: // javascript if (a &gt; b)...
7
by: John Nagle | last post by:
I've been parsing existing HTML with BeautifulSoup, and occasionally hit content which has something like "Design & Advertising", that is, an "&" instead of an "&amp;". Is there some way I can get...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.