By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,187 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,187 IT Pros & Developers. It's quick & easy.

asp & xls

P: n/a
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
Share this Question
Share on Google+
8 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.