Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000 worksheet where I want to convert date format | Excel Discussion (Misc queries) | |||
Format date in excel 2000 like '31st January 2005' | Excel Discussion (Misc queries) | |||
Format date in excel 2000 like '31st January 2005' | Excel Discussion (Misc queries) | |||
Australian date format in Excel 2000 | Excel Discussion (Misc queries) | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) |