pivottable number field not available

[itprsn]

My data is as follows: 7/9/2008 12:44:32 AM. I need to use the number option in pivottable field (lower right) to format this field as the time alone - but I don't have access to the number button - ??

 

[SkipVought]

Hi,

ALL of the data in the Source Data column, must contain a valid date/time value.

Once you have created a PT that has INVALID data, OR if you add a row with INVALID data or change valid data to INVALID data, your PivotCache is missed up.

You'll need to...

1) clean up your source data and
2) run this procedure...

Sub CleanMyPivots()
'SkipVought/2007 Apr 30/817-280-5438
'--------------------------------------------------
'cleans up pivot tables having OLD/UNUSED data hanging around.
'--------------------------------------------------
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
       pc.MissingItemsLimit = xlMissingItemsNone
       pc.Refresh
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[itprsn]

I'm not sure I set up/ran the procedure correctly, it didn't appear to do anything...


It appears this is related to blank lines. When selecting my data I selected columns, not specific rows/columns. I really do need (I think) just columns because the number of rows is variable - but I want the pivot table to ignore any blank lines because they cause a problem with the pivottable field not having number as an option. Is this possible?

 

[SkipVought]

Why do you have "blank lines" in your table?

There can be NO INVALID DATA in a Date column. Dates are NUMBERS. ZERO is a valid date. BLANK is INVALID.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

You cannot select ONLY COLUMNS. You must select a range of rows and columns that defines your table. A valid table has NO EMPTY ROWS OR COLUMNS.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]