Excel 2000 VBA date format changes in subtotals
Kris,
One workaround is convert your dates to text e.g.
=DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1) etc.
HTH
Reijo
-----Original Message-----
Hi,
Thanks, but I think you're missing the point ...
the column "A"
1/03/2004
1/03/2004
1/03/2004
5/03/2004
7/03/2004
7/03/2004
When I use Data-Subtotals from the menu I get
1/03/2004
1/03/2004
1/03/2004
Totaal 1/03/20004
5/03/2004
Totaal 5/03/2004
7/03/2004
7/03/2004
Totaal 7/03/2004
which is just fine (BTW: Totaal is dutch for total)
When I make the subtotals from VBA (or a macro that I
recorded) I get
1/03/2004
1/03/2004
1/03/2004
Totaal 3/1/20004
5/03/2004
Totaal 3/5/2004
7/03/2004
7/03/2004
Totaal 3/7/2004
Which is confusing.
Changing the dateformat on these cells will not help,
because it's not a
date, it's text. Anyway, the numberformat on these cells
already is
"d/mm/yyyy".
To me it looks like it sees dates in US notation when
run from vba.
"GJones" wrote in
message
...
Hi Kris;
You can use the following call
Columns("A:A").Select
Selection.NumberFormat = "d/mm/yyyy"
Thanks,
Greg
-----Original Message-----
Hi,
I'm automating the layout task of a specific excel
workbook in VBA
One of the tasks is to create subtotals whenever the
first column containing
dates change. These dates are formatted in d/mm/yyyy.
Getting the subtotals is no problem , it inserts rows
with the text
Totaal <date
but <date is now formatted as m/d/yyyy.
When I make the subtotals myself using the menu
Data ..subtotals there is no
problem. Then the date is in the same format,
d/mm/yyyy.
When I record this action as a macro and execute the
recorded macro, I again
get the dates formatted in the wrong format
(m/d/yyyy).
I have a dutch version of excel.
Application.international(xldateorder) gives 1,
meaning
d/m/yyyy...
How do I get the subtotal so that the date remains in
our
local format and
doesn't change to us format.
Regards,
Kris
.
.
|