Pivot Table Change to US Date Format - Why?
in [General]
|
From: Louise on 22 Apr 2010 17:31 Hi, I have a set of pivot tables which are being updated by cell the date in cell B1 using the following code: ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice Month").CurrentPage = ActiveSheet.Range("B1").Value At last this is working :) However... the dates are being inserted into the pivot tables as new selections in US date format instead of selecting an existing UK date format from the data choices. I do not understand this as all source data is in UK date format, cell B1 is in UK date format, the pivot field is also in UK date format, so why does this do this? I appreciate someones help!!!
From: Roger Govier on 23 Apr 2010 14:16 Hi Louise Try = Format(ActiveSheet.Range("B1").Value,"dd mmm yy") -- Regards Roger Govier Louise wrote: > Hi, > > I have a set of pivot tables which are being updated by cell the date in > cell B1 using the following code: > > ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice > Month").CurrentPage = ActiveSheet.Range("B1").Value > > At last this is working :) However... the dates are being inserted into the > pivot tables as new selections in US date format instead of selecting an > existing UK date format from the data choices. I do not understand this as > all source data is in UK date format, cell B1 is in UK date format, the pivot > field is also in UK date format, so why does this do this? > > I appreciate someones help!!! >
From: Louise on 26 Apr 2010 08:04 Hi Roger, Thanks for your reply, can I ask where in my code should i place this to get it to work? Kind regards, Louise "Roger Govier" wrote: > Hi Louise > > Try > = Format(ActiveSheet.Range("B1").Value,"dd mmm yy") > -- > Regards > Roger Govier > > Louise wrote: > > Hi, > > > > I have a set of pivot tables which are being updated by cell the date in > > cell B1 using the following code: > > > > ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice > > Month").CurrentPage = ActiveSheet.Range("B1").Value > > > > At last this is working :) However... the dates are being inserted into the > > pivot tables as new selections in US date format instead of selecting an > > existing UK date format from the data choices. I do not understand this as > > all source data is in UK date format, cell B1 is in UK date format, the pivot > > field is also in UK date format, so why does this do this? > > > > I appreciate someones help!!! > > > . >
|
Pages: 1 Prev: How do I find duplicate entries in a column in an Excel worksh Next: Excel VBA 'Help' |