Excel 2000 VBA date format changes in subtotals
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 |
Excel 2000 VBA date format changes in subtotals
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 . |
Excel 2000 VBA date format changes in subtotals
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 . |
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 . . |
All times are GMT +1. The time now is 06:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com