Date with leading zero... Excel is being a smart-***

[Glory]

"01/01/2011" consistently appears as "1/01/2011", no matter what I do.

I can format the display format of a date in a pivot field as mm/dd/yyyy. But I can't format the date as it appears in the pivot field's drop-down list.

This is causing problems with code I'm using to select certain dates from the page field.

Any ideas why nothing I do to format the date will make it appear correctly?

 

[Glory]

The source-range is formatted correctly, and I've refreshed and even recreated the pivot table.

I have another pivot table in front of me that's formatted exactly the way I need. Copying that pivot table over shows the dates in the right format... so I know it's possible, I just don't see how to make it happen.

Any help would be appreciated.

 

[Norie]

Are you sure it's this formatting causing the problem in the code?
How are you trying to refer to the dates in the dropdown?

 

[Glory]

Yes... I am absolutely positive it's the formatting, because when I change the code so that the literal drops the leading zero, it works just fine.

That's just the thing, I don't see any way to edit the format of the drop down list entries themselves. They seem to have a mind of their own.

Editing the cell format for the field doesn't help. Editing the source range doesn't help. The source range and the actual pivot field cell will show the correct format, but the dropdown list still doesn't show the entries correctly, which prevents the code from recognizing matches.

All the cells in question have the custom format "mm/dd/yyyy". The pivot dropdowns simply refuse to comply, and I just had to change a line in my code to compensate.

Len(fourtharray(0)) < 2 Then: fourtharray(0) = "0" & fourtharray(0)

became

Left(fourtharray(0),1) = "0" Then: fourtharray(0) = Replace(fourtharray(0),"0","",1, 1)

Personally I prefer the leading zero on months whose values are less than ten. But Excel won't let me do what I want to do... so what can I do, you know.

 

[Norie]

Can you post the code?

 

[Glory]

Sheets("Sheet").PivotTables("PivotTable").PivotFields("Test").CurrentPage = "04/12/2011"

Running as-is fails. The error received reads "Unable to set the _Default property of the PivotItem class".

Dropping the red character causes the code to succeed, which is what makes me think it must be the format I'm seeing in the dropdown list tht's causing the problem.

I read in the first couple hits on google that trying to set the current page can be tricky because the default properties of certain objects don't pan out.

Like, if you try to use a range value, you actually have to append ".value", otherwise it thinks you're trying to make the current page a range object.

So I tried adding the date to a new collection and then going "theCollection(1).Value", and it just spat out "Object required".

There are an awful lot of PivotField properties. Maybe I'm just missing the one I need?

 

[Norie]

What are you actually trying to do?

Is the field a page field?

 

[Glory]

Yes, it is.

There are a lot of different dates in the source range. I'm trying to select a bunch of dates from the "Date" field so that I can print a lot of different spreadsheets automatically.

 

[Norie]

The only way I could replicate your error was when the field I tested on wasn't a page field.

Even when it was a page field setting the CurrentPage just changed the value, it didn't change what other data displayed. (I think you found that anyway)

I managed something with filters and by simply looping through the pivot items of the field.

That was only for a 'normal' field though.

 

[Glory]

You've got the other setting tweaked then... I can't remember what it is, and I can't track it down again. It dis/allows overwriting of page-type pivotfields. Edit: I know its possible to have that setting off because I'm getting that error with my new pivot table. The old one still allows overwriting though, I just don't know where the setting is located. It's probably programmatic-only.

You're right, I encountered that with the original pivot table I mentioned (the one that preserves the leading zeros in short months, even though I don't know why it does). That original table would also allow me to overwrite the pivotfield's current page with a string that didn't match any pivot item in its collection.

I actually didn't want to be able to overwrite, because if an item wasn't present that would skew the table. So I had to create lists of unique data (which got way more efficient when I started using Advanced Filter... your suggestion, you might recall), then iterate through to check for missing items and add them into the source range.

MrKowz has suggested using "CDate" to convert the variable to a true date. That seems to work great. I was originally just going to go with the modified code I posted above, but like I said I like the leading zero.

Thanks for looking into it.