Excel2007 VBA - dates in Pivot Tables not read in correct format

If I create a PivotTable with a field called DATE containing dates formatting 
as dd/mm/yy, I then use this macro to remove Saturdays and Sundays from the 
PivotTable.  Except it reads the dates a mm/dd/yy until they are 'out of 
scope' and then they are read as dd/mm/yy.  Why?

Sub HideWeekendsFromPivotTable()
    Dim pivotName As String
    Dim pivotDate As Date
    Dim z, pivotCount As Long
    
    pivotName = ActiveSheet.PivotTables(1).Name
    
    With ActiveSheet.PivotTables(pivotName).PivotFields("Date")
        pivotCount = .PivotItems.Count
        
        For z = 1 To pivotCount
                    
            If Weekday(.PivotItems(z).Name) = 1 Or 
Weekday(.PivotItems(z).Name) = 7 Then _
                .PivotItems(z).Visible = False
                
        Next z
    End With
End Sub
0
Utf
1/10/2010 4:03:01 PM
excel.programming 6508 articles. 2 followers. Follow

0 Replies
1130 Views

Similar Articles

[PageSpeed] 54

Reply:

Similar Artilces:

Problem: new calendar entries land on wrong date
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange When making a new calendar entry, it will not land on the date I select, but rather on: March 20, 2020!!!! <br><br>The only way in which I can make a calendar entry is to make it in iCal, and then let iCal synch with Entourage calendar. <br><br>Likewise, the Entourage calendar, when printing, will only show/print imported holidays, not the actual entries as displayed. <br><br>What's wrong? <br> I have re-installed Office from scratch - no imp...

Help: Money Transfer Transaction Dates
I was using Money 2001 and recently upgraded to Money 2004. One the issues i've had with both Money and prior versions of Quicken is the inability to accurately record the dates for money transfers from one account to another (especially from a Checking account to a Credit Card) where the posting dates are different. For example... If i transfer x dollars from my checking account to pay off my MasterCard... my bank statement shows the transaction occuring on 01 OCT 2003 whereas my credit card statement shows the transaction occuring on 30 SEP 2003. When entering the transfer / payment...

Reading Pane Settings
I did a search for an answer to this question and have not found one. If I missed an answer already posted, please direct me to the right place. I have Outlook 2007 and set up the "right" reading pane for my e-mail. Now when I go to my calendar it is set up the same way - calendar on the left (same size as the inbox) and then there is a blank space to the right that is the size of the reading pane/preview. To the right is the to-do bar (which I have no problem with). I don't know why there is a reading/preview pane on the calendar when there is nothing to preview, and I...

cannot update. DB or object is read-only 3027
I use Access2007. The codes are: str = "select StudID, Email from qryTest;" Set rst = DB.OpenRecordset(str, dbOpenDynaset, dbSeeChanges) rst.MoveFirst Do While Not rst.EOF Call ManipulateData(rst("StudID")) 'call a sub-procedure rst.Edit rst("Email") = True rst.Update rst.MoveNext Loop rst.Close Set rst = Nothing DB.Close Set DB = Nothing ---- The error pops up at rst.Edit saying "cannot update. Database or object is read-only 3027". Can you help me to identify the problem? Thank you. "Ti...

Recommendations for an Excel and VBA book
Can anyone recommend a really good Excel reference book? In addition to the Microsoft guides, I have purchased several books and find them either too basic to be of help when the going gets tough, or a little too hard to understand (usually because they use terms and operators without clearly defining their function and/or limitations). Excel 2000 VBA by John Green is the best I have found, but it is just about 1 notch above my understanding in many areas. I have learned more from this newsgroup and the programming newsgroup than from any other source, and you are all appreciated. Thanks i...

lookup date, sum up total...
Hello all, Here is my situation: Worksheet 1 A B C D 1.)1/1/05 Data Data P&L 2.)1/2/05 Data Data P&L .. .. .. 365.) 12/31/05 Data Data P&L ............................................................ Worksheet 2 A B 1.) January =IF(Worksheet1=January, then sum all january P&L (Ignore other months)) 2.) February =IF(Worksheet1=February, then sum all February P&L (Ignore other months)) .. .. .. 12.) December =IF(Worksheet1=December, then sum all DecemberP&L (Ignore other months)) what formula do I need to...

Recipient address rejected: User unknown in virtual mailbox table?
I'm having issues with sending an email from an outside service to my Exchange server email addresses. When the outside service attempts to send the email it gets the following error returned: Error: 550 : Recipient address rejected: User unknown in virtual mailbox table The outside service believes this is being returned by Exchange. Does this sound familiar to anyone? On Sat, 20 May 2006 06:57:01 -0700, Achilles Heal <AchillesHeal@discussions.microsoft.com> wrote: >I'm having issues with sending an email from an outside service to my >Exchange server email addre...

Conditional Formatting #49
Is there a way of doing Conditional formatting, based on more than three criteria? I want a cell colour to change to one of 10 colours depending on the lookup value of another cell! (ie cell A2 will contain a name, and cells A3 will then contain the team, which it has looked up) Thanks Shane ...

Try on this correction package from M$
--hdloimxhzdpjfgclq Content-Type: multipart/related; boundary="patkxqwunzcvf"; type="multipart/alternative" --patkxqwunzcvf Content-Type: multipart/alternative; boundary="yoiovdjuorcngf" --yoiovdjuorcngf Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "September 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to p...

Changing to new dates
Hi everyone I am trying to change last year of date to new year i.e from 2003 to 2004.I have done them day by day and if i start changing date i have to do for 365 days .Can any one tell me how can i change those years e.g 01/01/0 02/01/03 this goes up to 31/12/0 and i want it to be changed to 2004 year plz help me im going crazy!!! Just put the first date in the first cell assume it is A1, in A2 put =A1+1 then just copy down the formula -- Regards, Peo Sjoblom "Ali" <anonymous@discussions.microsoft.com> wrote in message news:8B773478-C8A1-4F88-AE9D-CD3DD5B8878...

date subtraction difficulty
I'm trying to subtract dates, to calculate time duration. I'm subtracting column A from column B into column C. Column C is formatted 'm "mos", dd "days"' and columns A and B are formatted 'mm/dd/yy/@' Unfortunately each of the durations is 1 month too long. That is, if the real duration is 4 days, column C will display '1 mos, 4 days' Any clue what I'm doing here? Thanks much. On Tue, 18 Jul 2006 11:58:10 -0500, Peter <facetious_nickname@hotmail.com> wrote: >I'm trying to subtract dates, to calculate time durati...

Excel "Insert Comment" Formatting
Hi, Windows XP Home Excel 2002 How can I format the "Insert Comment" .....(open Workbook > right click on a cell > left click > Insert Comment)......in my Excel worksheet? It opens now at Font Size 8. I would like it to open at a larger Font Size and also have the Insert Comment window saved at my chosen larger size (I know how to expand to a larger size but want to save it at that size for next time using Insert Comment in a different cell). Thank you, Larry Larry Start > settings > Control Panel > Display > Appearance Tab > Advanced. Select...

PIVOT CHART LABELS
I have a PivotChart bar chart. The axis fields are 2 fields. The text from field 1 is aligned vertically on the chart, the text from field 2 is horizontal. I want both text labels to be aligned horizontally. Please help!! Hi, Unfortunately you can not change the alignment of nested axis labels. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Killer Janis B" <Killer Janis B@discussions.microsoft.com> wrote in message news:22B3A9B1-0537-44CB-A840-EFB9E91E3565@microsoft.com... >I have a PivotChart bar chart. The axis fields are 2 fields....

Format a single digit like "007"?
In the query result, I need to see (for example) a single digit number (say that "7") as "007". What is the function for this? I use FormatNumber function for other purposes. Can you help me please? try the following: format(7,"000") 007 format(77,"000") 077 format(777,"000") 777 "mezzanine1974" wrote: > In the query result, I need to see (for example) a single digit number > (say that "7") as "007". > What is the function for this? I use FormatNumber function for other > purposes. > Can you he...

Pivot Table row fields
Is there any way to uncheck all of the boxes at once in my row field drop down box. Example: I have dragged years into the row field. A drop down arrow shows but everything is checked. I do not want to have to uncheck these one at a time. Thank you for your help. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ There's some sample code here that shows/hides items in a pivot table: http://www.contextures.com/xlPivot03.html#Show97 Clys wrote: > Is there ...

Initilizing the VBA libraries (126)
I was wondering if someone could help with the following problem: I run Excel 2000. I have never in the years had any problem but I want to open a file that I need and as it goes to open it says an error occurred initilizing the VBA libraries (126). What does this mean and is there any way to fix this? Thanks very much. Hi, Check out the MS support at http://support.microsoft.com/default.aspx?scid=kb;en- us;270027&Product=xlw2K HTH >-----Original Message----- >I was wondering if someone could help with the following >problem: > >I run Excel 2000. I have never i...

Memory Cell Could Not Be "READ."
I have Money 2K, WINXP Home, and until about a week ago I had no problems updating. Now, most of the time, when I try to update the Internet Information, I get an error message which reads: The instruction at "0x600bb01c" referenced memory at "0x00000007" The memory could not be "read." Click OK to terminate the program. When this happens, my modem lights flicker as if it is trying to get a response, but isn't getting one. After 10 seconds or so, I get the above message. Every few days it works fine. I have Fidelity as a investment, and I can always...

Access 2000 File Format in Access 2003
Dear all, If I'm using Access 2003 to create Access 2000 format, is there any new feature in Access 2003 which I can't apply in Access 2000 file ? Thx. Vensia On Wed, 9 May 2007 14:20:23 +0700, "Vensia" <vensia2000_nospam@yahoo.com> wrote: >Dear all, > >If I'm using Access 2003 to create Access 2000 format, is there any new >feature in Access 2003 which I can't apply in Access 2000 file ? >Thx. there are many; however, are you deploying to users with Access 2000, or with Access 2003? The default format for your database is (normally) 200...

table hierarchy inconsistency
I have installed some new reports into my GP implementation and I get the following message when I try to run one of them (after the destination selection form). Table Hierarchy Inconsistency I do NOT get this in development - only after I install the chunk file into my test environment. Any idea where to look for this inconsistency? Don DeVeux This is usually caused by you adding a new relationship to an existing Great Plains table. Because the table is an original table (ID < 22000), it is not extracted into your chunk and neither is the new relationship. So when your report ...

Time Format to Text Output
Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 1...

what is 'too many different cell formats' reply?
Tommy Read this KB article On Thu, 16 Dec 2004 19:29:01 -0800, "tommy" <tommy@discussions.microsoft.com> wrote: Check these out: http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 http://www.vbapro.com/xl_formats.htm tj "tommy" wrote: > I think Gord meant to include this article link: XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/?id=213904 Gord Dibben wrote: > Tommy > > Read this KB article > > > > On Thu, 16 Dec 2004 19:29:01 -0800, "tommy" <tommy@discussions...

2007 Table Linked Charts
I have a dataset with monthly data over several years. I want to chart only the current calendar year's monthly data, and have the chart update automatically as each of the newest month's data is added. One solution is to hide the prior years' monthly data rows in the table which removes them from the chart. This limits other charts that might be linked to the same table where they chart the whole or an earlier date range of the dataset. Linking tables doesn't work since the mirroring tables don't auto update as a new month is entered. Has anyone developed a better sol...

How to have a correct size in a form?
Hi. I have a form which will open when I press a button in a other form. But when that form will open, it has various sum of data. That's why the form is not automatically in right size. Sometimes it is too large and sometimes to small. Is it possibe to make it open so that it will always be in a right size? Hannu On Wed, 04 Jul 2007 11:51:36 -0000, hanski <lappalainen.hannu@luukku.com> wrote: >Hi. > >I have a form which will open when I press a button in a other form. >But when that form will open, it has various sum of data. That's why >the form is not auto...

Microsoft 2003 Error Message: cannot read file
I was using Microsoft 99. Recently upgraded to Microsoft 2003. When I try to open money.mny I get the following message: Money cannot locate 'C:\Documents_and_Settings\MyDocuments\Money\Money.m ny' or cannot open it, possibly because it is a read-only file or you do not have permission to change it or your disk drive is write protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and open your most recent backup file. If the file is being used by another application, close it in the other application and click OK to try again. 1...

delete pivot table
Hello, Is there a way to delete a pivot table from a worksheet. Right now my method is to delete the whole sheet the pivot table resides on. thanks bill Select the pivot table and do edit>clear>all -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "billq" <bill.quigg@comcast.net> wrote in message news:c7udneOu4-eac3bcRVn-sA@comcast.com... > Hello, > Is there a way to delete a pivot table from a worksheet. Right now my > method is to delete the whole sheet the pivot table resid...