Excel 2000 VBA date format changes in subtotals

[Kris Ernst]

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

 

[GJones]

Hi Kris;

You can use the following call

Columns("A:A").Select
Selection.NumberFormat = "d/mm/yyyy"


Thanks,

Greg

 

[Kris Ernst]

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.

 

[rpt]

Kris,

One workaround is convert your dates to text e.g.
=DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1) etc.

HTH
Reijo